Clever Geek Handbook

Search function does not work with unsorted list

Why it works and evaluates the values ​​correctly.

enter image description here

but as soon as I change the order of the values, it gives the wrong values?

enter image description here

+3


source to share


1 answer


If you read the notes on the LOOKUP function it says:

The LOOKUP function will only work if the data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP or other related functions if the data is not sorted.

Change the formula to use VLOOKUP as follows:



=VLOOKUP(D3, A1:B6, 2, FALSE)

Syntax:

VLOOKUP (search_key, range, index, [is_sorted])

search_key - value to search. For example 42, "Cats" or I24.

range - a range to consider when searching. The first column in the range is searched for the key specified in search_key.

index - the index of the returned value column, where the first column in the range is numbered 1.

is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the search for the column (the first column of the specified range) is sorted.

+4


source






More articles:


All Articles