Lookup Formula: why does this work

**ChemistB** · 11-10-2009, 10:58 AM

In A previous post,

http://www.excelforum.com/excel-gene...ain-value.html

in which the OP was trying to find the maximum date (Col B) in which the value in Col A occurred, DonkeyOte used this formula as a possible solution

Please Login or Register  to view this content.

I understand the formula somewhat; 1/Sheet1!A1:A100=A1 will give either Infinity (or #DiV/0) or a 1. However, since the values in A are not in order (thus giving a search of something like 1, #DIV, # DIV, 1, ...) how would the formula know when the last value is? Does it go to the end and work backwards?

Thanks

**NBVC** · 11-10-2009, 11:02 AM

Here is one of my previous explanations to that....

http://www.excelforum.com/excel-gene...ml#post1997590

**DonkeyOte** · 11-10-2009, 11:02 AM

Key points re: LOOKUP function

a) it will return last value <= criteria

b) it ignores any/all values within the lookup_vector not of the same data type as that of the criteria value - note (importantly) this includes errors.

c) it assumes lookup_vector is sorted in ascending order at all times (regardless of reality) and thus assumes last found value to be the biggest

So using the example

=LOOKUP(2,1/(Sheet1!$A$1:$A$100=$A1),Sheet1!$B$1:$B$100)

the lookup_vector we know will be populated with only 1 or #DIV/0! - the criteria set to 2 is thus bigger than all possible numerical values in lookup_vector - given the nature of LOOKUP we know then that this will ensure the last numerical value (1) is returned... or where the result_vector is in use (as is the case here) the associated value.

In the link - we know the dates are listed in ascending order and thus the last instance is the max date - on that basis we use LOOKUP to find last instance of criteria (where 1/Boolean returns 1) and return associated date.