Let me explain a few poorly documented things about LOOKUP. First, it has some array processing capability. This was included for compatibility with other spreadsheet programs. Second, it ignores errors in the lookup array. And finally, it assumes that the lookup array is in sorted order, so as soon as it finds the position in the array where the lookup value is >= one position, and < the next position, it stops. But if the lookup value is > every value in the lookup array, then the last non-error value is selected.
So let's look at how the formula works in the B3 cell.
=LOOKUP(2,1/(
($F$2:$F$10<>"")*SEARCH($F$2:$F$10,A3)),$E$2:$E$10)
For the part in red, I wanted to let you have empty rows at the end of your Location Key table (F6:F10 here), so I make sure that I'm only looking at the non-blank rows. The part in red generates an internal array that looks like this: {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}.
=LOOKUP(2,1/(($F$2:$F$10<>"")*
SEARCH($F$2:$F$10,A3)),$E$2:$E$10)
Here the part in red looks for each value in the range F2:F10 within A3, and returns the position where it finds it, or a
#VALUE ! error. Searching for "" in A3 returns a 1. So the resultant array of doing 9 consecutive SEARCHes on A3 is {
#VALUE !;
#VALUE !;
#VALUE !;10;1;1;1;1;1}.
=LOOKUP(2,1/(($F$2:$F$10<>"")
*SEARCH($F$2:$F$10,A3)),$E$2:$E$10)
The result of multiplying 2 arrays in this manner is that the values in the same position in each array are multiplied together. Also, FALSE=0, and TRUE=1, so the resultant array is:
{
#VALUE !;
#VALUE !;
#VALUE !;10;0;0;0;0;0}
=LOOKUP(2,
1/(($F$2:$F$10<>"")*SEARCH($F$2:$F$10,A3)
),$E$2:$E$10)
Now we take the reciprocal of each value in the array, giving: {
#VALUE !;
#VALUE !;
#VALUE !;0.1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}. As you can see, there are
#VALUE ! errors where the values in F1:F3 were not found, 0.1 where the value in F4 was found, and #DIV/0! where F6:F10 were empty.
Taking the reciprocal has 2 purposes: first, it converts the 0s from the previous step into #DIV/0! errors, which we ignore. Second, the result from SEARCH when it finds a match is the offset into the string where the match is found. So here, GPH is found in the 10th position of A3. Taking the reciprocal of a number >=1 will always be a number <=1. That's why we got the 0.1.
So after all that manipulation, we've kind of arrived at the documented usage of LOOKUP. We try to look up 2 in the lookup array from the last step. As I said before, LOOKUP ignores errors. And if it can't find the exact number, but it's greater than anything in the array, it will return the position of the last number in the array. Since we cleverly managed to make sure that every number in the array is <=1, we can search for 2 and be assured we'll never find it, so LOOKUP returns the position of 0.1 in the array, then returns the value from the result vector in the same position.
Whew! Some tricky things going on in a fairly short formula. Trace it through Evaluate Formula if you want. I don't claim credit for it, the original version has been around for a long time.
Hope this helps!
