The mysterious use of LOOKUP

Question

I have been using this Excel formula for many years. It works great, but I want to understand why it works! The formula is used to find the last associated value in the list. For instance:

The formula in C14: =LOOKUP(2,1/(B1:B12="meat"),C1:C12)

Here the formula in C14 looks in column B for the last labeled cell "meat" and returns the associated value in column C. He finds "meat" in B9 and accordingly returns the value in C9 .

The most mysterious part of the formula is "1/(....)" . What is this division? Where does this syntax come from? Can it be used elsewhere? Why is the search value 2 ?

Answer 1

Here's what happens: This

 =LOOKUP(2,1/(B1:B12="meat"),C1:C12) 

becomes this

 =LOOKUP(2,1/{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},C1:C12) 

which becomes this

 =LOOKUP(2,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!},C1:C12) 

Part B1:B12="meat" is evaluated as an array of TRUE and FALSE values. When you do the math on TRUE or FALSE, it turns into 1 or 0. Splitting 1 into 1 or 0 returns 1 for all truths and div / 0 for all falsities.

Now that you know that you will have an array filled with exactly two things (either 1 or div / 0), you can LOOKUP any number that is greater than 1, and it will return the last 1 in the list, you could =LOOKUP(800,...) , and it will still return "the largest value that is less than or equal to the search value" or in your case 1.

Thus, two somewhat arbitrary - this is just a number greater than 1. The key point is to create an argument to the lookup array, which includes only 1 s and errors - LOOKUP ignores errors.

Binary search

I do not have official documentation on this, so here is an unofficial version. LOOKUP, HLOOKUP and VLOOKUP have an argument in which you specify the function if the data is sorted. If this argument is False, the function scans each record to the end. If this argument is True or omitted, the function uses a binary search. He does this - the argument exists only because the binary search is faster than the one-on-one search.

Binary search works by finding the average value and evaluating it by the desired value. If the average value is greater, everything to the right is discarded - the desired value should be on the left. He occupies the left half and finds the average value of this. If he is bigger, he drops the right and holds the left. Keep repeating until you find the meaning.

So, why did LOOKUP(2,{1,1,#DIV/0!,1,1,#DIV/0!,1,1},...) find the last 1 instead of any arbitrary 1 ? I do not know exactly how MS implemented its binary search, so I have to make some assumptions. My assumptions are that error values ​​are thrown from the array and that if there is an even number of records (without an average value), the value to the left of the middle is used. These assumptions may be wrong, but they have zero effect on the result.

Throw mistakes first. Now you have these records and their starting positions.

 1 1 1 1 1 1 1 2 4 5 7 8 

The "average" number is 1 (4). It is less than 2 (the desired value), so we throw everything to the left and process the right. Now we have

 1 1 1 5 7 8 

The average value is 1 (7). This is less than 2, so we throw everything to the left and process the right. Now we have

 1 8 

We get to one entry, so the answer. If the search value is greater than all other values, the binary search will always return the last value.

Answer 2

The logic is as follows:

• B1:B12="meat" returns an array of booleans, TRUE, where "meat" and FALSE otherwise
• 1 / TRUE returns 1, and 1 / FALSE = 1/0 returns # DIV / 0 !, so you get an array filled with 1 and # DIV / 0! as shown below. (This step is used to convert FALSE to an error value, for use in the next step.)

• LOOKUP execution for value 2 in this array fails because there is no 2, and because the values ​​are not sorted. So instead, LOOKUP searches for the last number number less than 2, ignoring any # DIV / 0! error values. What for? Who knows. This is an undocumented function not mentioned in the documentation for the LOOKUP function.

Thus, this sheikhanism relies on undocumented behavior to return the desired result.