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"
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.
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.
