Daniel from Quebec sends in a wild formula to solve the Last Match problem from Episode 1073. We'll look at using Evaluate Formula to study how the LOOKUP value actually works. Episode 1083 shows you how.
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.
Well, we're going to go back to episode 1073.
This is where Sarah from the cattle farm in England asked about how to find the last match. Remember, she had some vehicles, and this is the mileage for this record and then, over here she wanted a formula, that would find the mileage for the last time that vehicle was fueled.
So, E14 last occurred here, the mileage was 11728. We copy that down.
B11, last match was here, 12689.
And that gets copied down. All right well, I have a formula, and Daniel from Quebec sends in this formula, using the LOOKUP, instead of VLOOKUP.
And he's doing, looking at the number 2 and then 1/(A$1:A21= A22) and then the results vector is B1: B22 and.
I have to tell you, I looked at that, and instead there's no way that's going to work.
But any time that someone tells me that formula works, and it actually does, what do I do?
I come back here to the Formulas tab. I say, let's turn on Evaluate Formula, and see how this bad boy is actually working.
So, we do Evaluate Formula. First thing I want to do is, figure out what A22 is.
Well, that's going to be E14.
So, I get that and then it goes through all of these values.
Vehicle E14, F15 and compares it to E14.
That's what it's going to do, so in the next step, we're going to see a whole bunch of TRUEs and FALSEs.
Allright, FALSE, TRUE, FALSE, FALSE. All right.
Still don't see where this is going, and then I noticed that we're about to do 1 divided by this whole big array.
1 divided by this whole big array.
Well, TRUE is 1. So, 1/1 is going to be the number 1.
But FALSE is actually stored as a 0. So, 1/0 is going to generate a whole bunch of errors and sure enough, when I do evaluate, you see that we get a whole bunch of DIV|0| errors, but occasionally a 1.
And then, what the program is doing? It's saying, hey go find a TRUE within this array.
Well, ofcourse there are no TRUEs, but just like in episode 1073, where I used a MATCH function, what's going to happen is, it's going to keep looking for the TRUE.
It's never going to find a TRUE. So it's going to give me the last 1 in this array.
So, we'll evaluate that and sure enough, by finding that 1, the results vector is going to give us the corresponding row, and it works out beautifully.
I love this.
This is just one of those functions that, no one, whoever, looks at this spreadsheet, is going to be able to figure out. What the heck you're doing? It's like Magic.
It just simply works, so Daniel from Quebec.
I want to thank you for sending in that great formula.
Daniel, if you don't have an excel master pin, drop me a note.
And, we'll ship one up to Canada for you.
And, for everyone else, thanks for stopping by. Will see you next time for another net cast from MrExcel.
Well, thanks for stopping by. Will see you next time for another netcast from MrExcel.
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.
Well, we're going to go back to episode 1073.
This is where Sarah from the cattle farm in England asked about how to find the last match. Remember, she had some vehicles, and this is the mileage for this record and then, over here she wanted a formula, that would find the mileage for the last time that vehicle was fueled.
So, E14 last occurred here, the mileage was 11728. We copy that down.
B11, last match was here, 12689.
And that gets copied down. All right well, I have a formula, and Daniel from Quebec sends in this formula, using the LOOKUP, instead of VLOOKUP.
And he's doing, looking at the number 2 and then 1/(A$1:A21= A22) and then the results vector is B1: B22 and.
I have to tell you, I looked at that, and instead there's no way that's going to work.
But any time that someone tells me that formula works, and it actually does, what do I do?
I come back here to the Formulas tab. I say, let's turn on Evaluate Formula, and see how this bad boy is actually working.
So, we do Evaluate Formula. First thing I want to do is, figure out what A22 is.
Well, that's going to be E14.
So, I get that and then it goes through all of these values.
Vehicle E14, F15 and compares it to E14.
That's what it's going to do, so in the next step, we're going to see a whole bunch of TRUEs and FALSEs.
Allright, FALSE, TRUE, FALSE, FALSE. All right.
Still don't see where this is going, and then I noticed that we're about to do 1 divided by this whole big array.
1 divided by this whole big array.
Well, TRUE is 1. So, 1/1 is going to be the number 1.
But FALSE is actually stored as a 0. So, 1/0 is going to generate a whole bunch of errors and sure enough, when I do evaluate, you see that we get a whole bunch of DIV|0| errors, but occasionally a 1.
And then, what the program is doing? It's saying, hey go find a TRUE within this array.
Well, ofcourse there are no TRUEs, but just like in episode 1073, where I used a MATCH function, what's going to happen is, it's going to keep looking for the TRUE.
It's never going to find a TRUE. So it's going to give me the last 1 in this array.
So, we'll evaluate that and sure enough, by finding that 1, the results vector is going to give us the corresponding row, and it works out beautifully.
I love this.
This is just one of those functions that, no one, whoever, looks at this spreadsheet, is going to be able to figure out. What the heck you're doing? It's like Magic.
It just simply works, so Daniel from Quebec.
I want to thank you for sending in that great formula.
Daniel, if you don't have an excel master pin, drop me a note.
And, we'll ship one up to Canada for you.
And, for everyone else, thanks for stopping by. Will see you next time for another net cast from MrExcel.
Well, thanks for stopping by. Will see you next time for another netcast from MrExcel.
