Problem: VLOOKUP returns the first match that it finds. I need to get the last match in the data. In this figure, I want to lookup A and find the 12 from row 5, since that is the latest data for A.
- Find the last match for each letter.
Strategy: Use =LOOKUP(2,1/($A$1:$A$7=D2),$B$1:$B$7).
- No one at your office will have a clue what you are doing.
First, LOOKUP is an ancient function that Excel includes for backwards compatibility with Quattro Pro. It is a bizarre little function that takes a lookup value, a lookup vector, and a results vector. It always uses the Approximate Match version that you would get when using TRUE at the end of your VLOOKUP. Like the approximate match, LOOKUP expects the table to be sorted, but since you are using this formula to trick Excel, the table does not have to be sorted.
People end up using LOOKUP instead of VLOOKUP because LOOKUP works with arrays that VLOOKUP won't work with. Both this topic and the next topic show of the array-handling ability of LOOKUP.
This formula came from the MrExcel Message Board, originally posted to a MrExcel MVP named Fairwinds.
Let me explain the formula step by step, starting with A1:A7=D2. This comparison will produce a series of TRUE/FALSE values. In the figure above, you would end up with {TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}.
Next, the formula divides that array into the number 1. Flip back to Fig 399 to see that Excel treats TRUE like 1 and FALSE like 0. Of course, 1/1 is 1. But 1/0 is a DIV/0 error. After doing the division, you have a series of values that are either 1 or #DIV/0!: {1; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!}.
- From the Evaluate Formula dialog, after the fourth step.
If you flip back to Fig 460, you can see that the approximate VLOOKUP is ignoring text entries and error values. The same thing works here.
Also, in the last topic, there was a question if you should look for 9.99999999999999E+307 or simply 99999999. As you learned in the last topic, you just have to search for a number that is larger than any expected value. The logical test is either going to return 1 or #DIV/0!. There is no way that you will ever get anything larger than a 1 at this point of the formula. So, you can simply search for a 2.
When LOOKUP is searching for a 2 in {1; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!}, it can not find the 2. It thus uses the last numeric entry. In this case, it is the 1 that was calculated from cell A5. LOOKUP will return the fifth entry from the results vector. Since the results vector is B1:B7, Excel will return the 12 from cell B5.
Additional Details: The community of Excel aficionados at the MrExcel.com Message Board create some of the wildest formulas that I've ever seen. I took a collection of these formulas and put them in my book, Excel Gurus Gone Wild.