CODEWITHSUNDEEP
excelexcel-formula
apreg
apreg

Reputation: 637

How this lookup formula works?

I'd like you to explain why this formula works: =LOOKUP(2,1/(A2:A10=D2),B2:B10) I know about lookup, I know what this formula does (The following formula searches A2:A10 for the last instance of the value in D2, and returns the corresponding value from B2:B10...) but I don't understand how it works. For example what does this part do: A2:A10=D2 ? Here is the source where you can find the workbook also: http://www.xl-central.com/lookup-last-instance.html

Upvotes: 1

Views: 449

Answers (1)

user4039065
user4039065

Reputation: 

LOOKUP(lookup_value,  lookup_vector,  [result_vector])

The LOOKUP function is a very basic cousin of the HLOOKUP function and VLOOKUP function. The latter two provide additional functionality for selecting the row/column of data to return from their table_array parameter (row_index_num/col_index_num) as well as a range_lookup option which can force an exact match or an approximate (aka nearest) match when used with sorted data. The LOOKUP function has to have the result_vector (return row or column) specified if it is not the same as the lookup_vector and always returns the nearest match.

Excel treats TRUE as one and FALSE as zero when used mathematically. A 1 divided by 1 will equal 1 and a 1 divided by 0 is a #DIV/0! error. A #DIV/0! error will not match anything; not even another #DIV/0! error. Since you are looking for a 2, it finds something that might be the best match in row 2 (e.g. 1/1) but keeps looking since it is not an exact match. It finds another possible in row 5 but keeps looking. It finds another possible in row 8 and cannot find anything remotely close below that so it returns the value from column B in row 8. We are looking to match a 2 with a series on 1's and errors because we want the last nearest match.

For all intents and purposes, you are breaking the rules when using this type of formula as the lookup_vector is not in ascending order (which conventionally it should be). With the unsorted duplicate values we are achieving the correct results by relying on the function's 'broken' behavior when looking for a value it will never find; e.g. looking for a 2 in an array of 1's and errors.

Upvotes: 2

Related Questions