B5 = max(B5:B6) is 1 if B5 is the max, else 0

Dividing 1 thru that gives 1 or #Div/0, an invalid value.

Looking up value 2 in that results leads to accessing the row with the max value.

Solved

Posted on 2014-09-01

Hello,

This is a follow-up question to the above thread. The formula given (by

=LOOKUP(2,1/(B1:B12=B15),A

My question in

1/(B1:B12=B15)

I have seen (and I now use) a number of solutions — usually from

Thanks

2 Comments

B5 = max(B5:B6) is 1 if B5 is the max, else 0

Dividing 1 thru that gives 1 or #Div/0, an invalid value.

Looking up value 2 in that results leads to accessing the row with the max value.

I partially explained the technique in one of your other questions, but it's probably more appropriate here:

When you use this formula

=LOOKUP(2,1/(B1:B12=B15),A

Finding the appropriate value in one range and returning a value from another range is normal LOOKUP behaviour but the "trick" here is in how that match is made.

Note that there is no requirement for any range to be sorted for the formula to work as per my definition in bold. Of course for your scenario you want the

The formula works in a similar way to this formula

=LOOKUP(9.99E+307,A:A)

That finds the last number in column A. Again the range doesn't need to be sorted and could also contain error values or text - it still finds the last number in column A. Why?

For that you need to understand how LOOKUP behaves when it doesn't find the lookup value.

Because LOOKUP is supposed to work with an ascending range it can use "binary search" - only examining a limited number of values, e.g. it looks at the midpoint to see if the lookup value is > or < than that value, if it's greater it can discard the first 50% of the values and if it's < then it can discard the top 50%. The function keeps on examining the midpoint and discarding the other values - when the lookup value isn't found it's left with only one value - the last number in the range.

For this version the principle is the same - the lookup array will return only 1s or #DIV/0! errors so if you use 2 as LOOKUP value (2 is just a convention - it could be 20, 200, 2000 or 10000, any number > 1) it matches with the last 1 which corresponds with the last row where B15 matches:

Specifically if I have "x" in B15 and also have "x" in B4, B7 and B11 then

{FALSE;FALSE;FALSE;TRUE;FA

When you divide 1 by that array you get this as the lookup range:

{#DIV/0!;#DIV/0!;#DIV/0!;1

So the LOOKUP matches with the 1 at position 11 and returns the value in A11

regards, barry

Title | # Comments | Views | Activity |
---|---|---|---|

Need Excel Index/Match Formula to Dynamically Change Graph | 6 | 28 | |

how to link excel with word | 6 | 32 | |

Excel remove Chars in Conditional formating. | 6 | 19 | |

simple formula but cannot get it work. | 5 | 28 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!