lookup function doesn't work with unsorted list

google-sheets lookup
jakub picture jakub · Sep 28, 2014 · Viewed 13.9k times · Source

Why does this work, and looks up values correctly

enter image description here

but once i change the order of values, it produces incorrect values?

enter image description here

Answer

Bill the Lizard picture Bill the Lizard · Sep 28, 2014

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

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

Change your formula to use VLOOKUP as follows:

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

Syntax:

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

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

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

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

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

Related questions

How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?

Read More
Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More
How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.

Read More