google-sheetsgs-vlookup

GoogleSpreadsheet, VLOOKUP with an empty cell

I have this matrix for key/values

1 | one
2 | two
3 | three
  | none

and the VLOOKUP cell

=VLOOKUP(A10,A1:B4,2,false)

If the cell A10 contains any number from 1 to 3 it is ok, but if the cell A10 is empty the VLOOKUP returns: "#N/A" -> _"Error: Did not find value '' in VLOOKUP evaluation."_

Any possibility to make VLOOKUP to work also with empty cells?

Solution

  • I've found a solution here:

    =IF(ISNA(VLOOKUP(A10,A1:B4,2,false)), B4, VLOOKUP(A10,A1:B4,2,false))

    Check that B4 is the hardcoded position of the "none" text so this is extracted not using the VLOOKUP it self.

    I really hope someone comes with something less ugly ;)