Find Last Value in a Row: Podcast #1252

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Sep 24, 2010.
A question from my seminar at the NAESA conference in New Orleans (September 2010): "How can I return the last value entered in a particular row?" Episode #1252 will show you how. "Learn Excel 97 - 2007 from MrExcel"
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn self from MrExcel podcast episode, 1252-Find the last in each row.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I appreciate you putting up with weeks and weeks and weeks with podcast and support the new books.
We're now back to just some regular questions.
Sent in by people, either my seminars.
Or you, if you like it. If, you have a question like to see on the podcast.
Then send in "Bill@MrExcel.com.
Now, today's question is, someone has an array of numbers.
So, there's data for weeks going across and what they need here is the value from the latest week.
The difficulty though is for each item.
Some things are filled in other things, you're not filling.
All right now, let me show you, how we're going to solve this and why it works?
As we speak. Yeah, we look.
I'm sure a lot of you are familiar with, we look up the comma false version, if you look up.
But today I have an interesting situation here, where we're paying a bonus, and if you have a sale over $5000 you get a $5 bucks.
The sale is greater than $15000 you get a $30.
$25000 sale, a 100 bucks.
So, can we just think about it.
What bonus do we pay for this 12000 dollar, sale?
Well of course they get $15 bonus.
But, if we do, we lookup.
That value 12015 is not found in this table.
So, we need the comma true version, as we lookup.
Which says, hey! Go get the value just less than, other value that we are looking up.
So if we have to look up exactly 15000.
Well, it's found and that works out great.
But if we choose some other value like 25050, which isn't the table.
It will return the value just less.
Now, so let's take a look at this VLOOKUP, with a comma true at the end.
You can actually leave the true off.
If you want to, but I put it there just remind myself that this is a range version of VLOOKUP.
Now here's a really interesting thing about this type of VLOOKUP.
We ask for value smaller than, the smallest value like, 5.
It returns an N/A but, if we return.
If, we ask for a huge value, the largest value.
Just some value incredibly large.
It always returns the last item it affects.
All right! So, I'm gonna use that fact.
I'm going to do not a VLOOKUP here, but an HLOOKUP.
So, =HLOOKUP And I'm gonna look up a really large value now, it's your call what value put in here.
I'm just gonna put in on the obviously the numbers are between, like up to 99.
So, just asking for a whole bunch of 9's.
Certainly, is gonna be large enough and then for my table array.
I'm gonna choose, just this whole row of numbers here.
I'm gonna go out far enough.
So that way it'll be further than, they ever would possibly ask for and then, which row.
Well! I want this row and then (,1, True).
Saying that range matched and see what happens there, is it returns the '55'.
If, later on you come along and get another value 77.
That will update to show the next value and so kind of cool there.
We're using that true version of the VLOOKUP, or in this case an HLOOKUP, to between the last non blank value.
In that row for HLOOKUP, or we do it in a column, for VLOOKUP.
Intresting.
I want to thank you for stopping by.
See you next time, for another netcast.
MrExcel.
 

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top