24

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

jakub
  • 4,774
  • 4
  • 29
  • 46

1 Answers1

36

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.

Community
  • 1
  • 1
Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
  • Based on the documentation of `IFERROR`, if you keep your lookup table in a second page of the sheet, you can can have a default value for failed lookups like: `=IF(NOT(ISNA(VLOOKUP(B2,''!A:B,2, FALSE))), VLOOKUP(B2,''!A:B,2, FALSE), "")`, where column A is keys, and B is values, unsorted in sheet "". – ThorSummoner Jun 14 '17 at 17:22
  • VLOOKUP is not a substitute for LOOKUP because it requires the lookup value to be in the 1st column. – Nick Jan 08 '18 at 16:09
  • @ThorSummoner Maybe you meant to write this (since you mentioned `IFERROR`) but you can just do `=IFERROR(VLOOKUP(B2,')` – mboratko Mar 20 '18 at 19:34
  • 4
    @Nick `VLOOKUP` requires the lookup value to be in the first column of the *range* specified. The only situation it wouldn't substitute for `LOOKUP` is when the column of the desired result is left of the column of the lookup value. – Chuck Batson Apr 29 '18 at 16:31
  • @ChuckBatson Yeah, that's what I said. – Nick Apr 30 '18 at 20:34
  • 11
    Found the solution for doing an unsorted lookup where the result range is before the search range. INDEX(result_range,MATCH(search_key,search_range,0)) Source: https://yagisanatode.com/2017/11/27/vlookup-left-in-google-sheets-with-index-and-match/ – flyingsolow Aug 30 '18 at 18:45
  • 2
    @flyingsolow Thank! You should add it as an answer so it is seen more easily, because this fixes a really glaring inadequacy of LOOKUP. – Magne May 12 '20 at 18:01
  • @flyingsolow that should be the answer, solved my problem – beam022 Jul 30 '22 at 09:04