As a follow-up to Episode #1252, "Once you've used the HLOOKUP to return the last value in the row, can you find the date associated with that entry?" Yes, you can! Today, in Episode #1254, Bill shows us how to locate the last value in a row of data and return that value to our designated cell using the INDEX and MATCH Functions.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel Podcast episode 1254.
Where's the Last in Each Row?
Oh hey, welcome back to the MrExcel netcast.
I'm Bill Jelen Again, taking questions from folks, If you have a question shoot it into, Bill@MrExcel.com I want to follow up on episode 1252.
Where we used the HLOOKUP with comma true at the end of figure out where the last entry was.
Actually, what the last country was in each row and then the fall of question was.
Ok now, that I know the last entry I no, need to know the date.
The date is a heading up here in row one associated with that last entry.
Ok well, I know that we're going to start with equal index, because equal to index is a great way to return a value from a vector, like this vector.
So I know that we're gonna start that and I want to make sure that's locked down to row one.
So, I'm going to press F4, 1, 2 times to lock down.
The dollar sign 1 and dollar sign 1.
All right now, we need to tell it which row.
Well, that's easy and then which column that's going to be the hard part.
Now, HLOOKUP return the exact and value so, in this case returned the 58.
I need to return the position and if we need to return the position, where something is instead of HLOOKUP or BLOOKUP.
We're going to use a function called MATCH.
MATCH go look up, up again our really large value.
Just like we did in episode 1252.
So, I'm just gonna put in a few 9's there, in this range here that goes from D all the way out far enough to be more data than we're ever going to have and then comma true or a one.
True being the same as one also with one there, 2 closing parenthesis in the end which Excel did for us and format that as a date.
Ok, Ok ,All right so, this is saying it the 58 occurred on 10/4. Now let's copy that formula down.
We'll try it again.
So, here 77 Occurred on 9/20, that's working great. If we get another value. I'll just enter 11 there then it changes to 10/4.
So, what it's doing is it's looking for a really large value 999 999.
Since, we're using the comma true or comma 1 version when it can't find it, it returns the last numeric value in the range.
We used 8LOOKUP to do that on episode 1252.
Well now, we're using MATCH which tells us the position or occurred and then, using that position as the last argument in the INDEX Function to return a value from another row.
Again, INDEX is a function, a lot of people use a lot MATCH and certainly, MATCH with a comma true or comma 1 at the end.
Again, something we don't use a lot but that nice combination will very nicely solve this problem.
Okay, I thank you for stopping by.
See you next time with another netcast from MrExcel.
Learn Excel form MrExcel Podcast episode 1254.
Where's the Last in Each Row?
Oh hey, welcome back to the MrExcel netcast.
I'm Bill Jelen Again, taking questions from folks, If you have a question shoot it into, Bill@MrExcel.com I want to follow up on episode 1252.
Where we used the HLOOKUP with comma true at the end of figure out where the last entry was.
Actually, what the last country was in each row and then the fall of question was.
Ok now, that I know the last entry I no, need to know the date.
The date is a heading up here in row one associated with that last entry.
Ok well, I know that we're going to start with equal index, because equal to index is a great way to return a value from a vector, like this vector.
So I know that we're gonna start that and I want to make sure that's locked down to row one.
So, I'm going to press F4, 1, 2 times to lock down.
The dollar sign 1 and dollar sign 1.
All right now, we need to tell it which row.
Well, that's easy and then which column that's going to be the hard part.
Now, HLOOKUP return the exact and value so, in this case returned the 58.
I need to return the position and if we need to return the position, where something is instead of HLOOKUP or BLOOKUP.
We're going to use a function called MATCH.
MATCH go look up, up again our really large value.
Just like we did in episode 1252.
So, I'm just gonna put in a few 9's there, in this range here that goes from D all the way out far enough to be more data than we're ever going to have and then comma true or a one.
True being the same as one also with one there, 2 closing parenthesis in the end which Excel did for us and format that as a date.
Ok, Ok ,All right so, this is saying it the 58 occurred on 10/4. Now let's copy that formula down.
We'll try it again.
So, here 77 Occurred on 9/20, that's working great. If we get another value. I'll just enter 11 there then it changes to 10/4.
So, what it's doing is it's looking for a really large value 999 999.
Since, we're using the comma true or comma 1 version when it can't find it, it returns the last numeric value in the range.
We used 8LOOKUP to do that on episode 1252.
Well now, we're using MATCH which tells us the position or occurred and then, using that position as the last argument in the INDEX Function to return a value from another row.
Again, INDEX is a function, a lot of people use a lot MATCH and certainly, MATCH with a comma true or comma 1 at the end.
Again, something we don't use a lot but that nice combination will very nicely solve this problem.
Okay, I thank you for stopping by.
See you next time with another netcast from MrExcel.
