Sarah from the cattle farm in the UK writes in again with an insanely difficult question. Do a VLOOKUP, but find the last previous entry that matches the vehicle. Episode 1073 shows you a formula from Excel Gurus Gone Wild to solve this problem.
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:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is the strangest email I've ever gotten.
This is from Sarah.
Sarah is on the cattle farm in England.
And Sarah was on the podcast before. I answered one of her questions and as a thank you, they've now named one of the new cattle after me.
Is that the weirdest thank you, you've ever had?
Somewhere right now, in England, there's a head of cattle that is named after me.
So, Sarah sent in an insanely hard question and this comes directly from Excel Gurus Gone Wild.
I'm going to try and explain this.
These are vehicles here and this is the mileage when the vehicle was fueled.
And Sarah says, when they enter this vehicle and the mileage, she would like to go grab the previous mileage.
So that involves, finding the last entry for that vehicle and bringing that mileage down to here.
Right now, they're doing that completely manually and I have a formula that will do this.
But I need to explain what's going on.
Even, if it's just for my own personal, trying to understand how it works.
First of all, I want to talk about MATCH.
Now, MATCH is kind of like VLOOKUP, but not the VLOOKUP or MATCH that we usually do with comma False.
This is the exact MATCH.
The exact MATCH says, "Hey, the data over here is sorted and we want to find a MATCH for 952" and 952 says that, "That is in the 15th row of the range." Right?
That works out great.
Now, I want to show you a cool trick here.
What if we ask for a number that's way larger than any of the numbers?
So, just I'm going to put in a bunch of 9's.
That always is going to return the last cell in the range.
Cool trick, isn't it?
Now, I'm going to make this a little bit more interesting.
I'm going to replace some of these numbers with N/A's.
So, =N/A() and strange enough, the Match ignores those N/A's and tells me the last real number in the range.
Got it?
All right, now you're saying, "Well, how is this going to solve our problem?" I'm going to come back here to our spreadsheet and I've built an interesting formula.
It says, "Hey, we're going to use the MATCH of a really large number 99999" and then on the fly I'm going to build a new array.
Check to see if everything from, C$1 down to the row right above me C21 and then notice, there's no dollar sign there before the 21, is equal to C22.
If it is, put in the number 1, if it's not, put in the number N/A.
And so let's conceptualize what we're going to get here.
We're going to get an array of either 1's or N/A's. There'll be a 1 here, N/A, N/A, N/A, N/A, a 1, N/A, N/A, N/A, N/A and so on.
And then, I'm using that MATCH trick. Look for a really large number within that array and the array is going to be either 1's or N/A's.
It's going to tell me the position of the last matching number.
So, we have to use Ctrl+Shift+Enter because it's an array formula that tells me that on row 16 is the last C123 ABC.
All right, so now that I know that, I'm going to come back here and I'm going to use the INDEX function and say the INDEX of all of my fuel values in C and in, looks like at column G. Column G.
So, G1, G$1:G22, in which row number I want? I want the result of that MATCH.
Put the closing parenthesis. Ctrl+Shift+Enter and 28331. Now, we'll copy this up and just see how it matches what Sarah had manually done.
And as I go through it looks, like "Hey, look at that, it's working, all right." Just an insanely hard formula. Now, this N/A is saying that this particular vehicle, V83 HDL has not been fueled in this data set.
Sarah is going to have to deal with those herself. So, there you have it.
An Insanely hard formula that's straight out of Excel Gurus Gone Wild.
The book of some of the best amazing formulas that came out of the Mr Excel message board.
Want to thank Sarah for sending that question in. You know, last time I answered a question, I had a, a cattle named after me.
I really have no idea what the fallout will be but...
thats very cool. I want a picture. I don't know why.
And here's, this, never mind, it's completely insane.
All right, want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
Will see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is the strangest email I've ever gotten.
This is from Sarah.
Sarah is on the cattle farm in England.
And Sarah was on the podcast before. I answered one of her questions and as a thank you, they've now named one of the new cattle after me.
Is that the weirdest thank you, you've ever had?
Somewhere right now, in England, there's a head of cattle that is named after me.
So, Sarah sent in an insanely hard question and this comes directly from Excel Gurus Gone Wild.
I'm going to try and explain this.
These are vehicles here and this is the mileage when the vehicle was fueled.
And Sarah says, when they enter this vehicle and the mileage, she would like to go grab the previous mileage.
So that involves, finding the last entry for that vehicle and bringing that mileage down to here.
Right now, they're doing that completely manually and I have a formula that will do this.
But I need to explain what's going on.
Even, if it's just for my own personal, trying to understand how it works.
First of all, I want to talk about MATCH.
Now, MATCH is kind of like VLOOKUP, but not the VLOOKUP or MATCH that we usually do with comma False.
This is the exact MATCH.
The exact MATCH says, "Hey, the data over here is sorted and we want to find a MATCH for 952" and 952 says that, "That is in the 15th row of the range." Right?
That works out great.
Now, I want to show you a cool trick here.
What if we ask for a number that's way larger than any of the numbers?
So, just I'm going to put in a bunch of 9's.
That always is going to return the last cell in the range.
Cool trick, isn't it?
Now, I'm going to make this a little bit more interesting.
I'm going to replace some of these numbers with N/A's.
So, =N/A() and strange enough, the Match ignores those N/A's and tells me the last real number in the range.
Got it?
All right, now you're saying, "Well, how is this going to solve our problem?" I'm going to come back here to our spreadsheet and I've built an interesting formula.
It says, "Hey, we're going to use the MATCH of a really large number 99999" and then on the fly I'm going to build a new array.
Check to see if everything from, C$1 down to the row right above me C21 and then notice, there's no dollar sign there before the 21, is equal to C22.
If it is, put in the number 1, if it's not, put in the number N/A.
And so let's conceptualize what we're going to get here.
We're going to get an array of either 1's or N/A's. There'll be a 1 here, N/A, N/A, N/A, N/A, a 1, N/A, N/A, N/A, N/A and so on.
And then, I'm using that MATCH trick. Look for a really large number within that array and the array is going to be either 1's or N/A's.
It's going to tell me the position of the last matching number.
So, we have to use Ctrl+Shift+Enter because it's an array formula that tells me that on row 16 is the last C123 ABC.
All right, so now that I know that, I'm going to come back here and I'm going to use the INDEX function and say the INDEX of all of my fuel values in C and in, looks like at column G. Column G.
So, G1, G$1:G22, in which row number I want? I want the result of that MATCH.
Put the closing parenthesis. Ctrl+Shift+Enter and 28331. Now, we'll copy this up and just see how it matches what Sarah had manually done.
And as I go through it looks, like "Hey, look at that, it's working, all right." Just an insanely hard formula. Now, this N/A is saying that this particular vehicle, V83 HDL has not been fueled in this data set.
Sarah is going to have to deal with those herself. So, there you have it.
An Insanely hard formula that's straight out of Excel Gurus Gone Wild.
The book of some of the best amazing formulas that came out of the Mr Excel message board.
Want to thank Sarah for sending that question in. You know, last time I answered a question, I had a, a cattle named after me.
I really have no idea what the fallout will be but...
thats very cool. I want a picture. I don't know why.
And here's, this, never mind, it's completely insane.
All right, want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
Will see you next time for another netcast from MrExcel.
