Sheet ReserveSpreadsheets worth keeping.

Spreadsheet skills

VLOOKUP or XLOOKUP: which should I use?

Short answer

Use XLOOKUP when everyone who opens the file has it, meaning Excel 2021, Microsoft 365, Google Sheets, or LibreOffice 24.8 and later. It defaults to an exact match, looks left or right, and has a not-found argument. Use VLOOKUP when the file must work in Excel 2019 or older, or in older LibreOffice versions.

Updated

Both functions find a value in one column and return a value from the same row. XLOOKUP is the newer one and fixes most of VLOOKUP's weaknesses. The deciding factor is usually which software the readers of your file use.

What is the syntax of VLOOKUP and XLOOKUP?

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

How do VLOOKUP and XLOOKUP look up a SKU's price?

A B C
SKU Product Price
A-101 Desk lamp 24.50
A-102 Monitor stand 38.00
A-103 USB hub 19.95

Find the price of A-102 (the table occupies A1:C4):

=VLOOKUP("A-102", A2:C4, 3, FALSE)
=XLOOKUP("A-102", A2:A4, C2:C4, "Not found")

Both return 38.00. Now find the SKU for "Monitor stand". VLOOKUP cannot do this directly, because it searches only the first column of the table and returns values to its right. XLOOKUP can:

=XLOOKUP("Monitor stand", B2:B4, A2:A4)

This returns A-102.

What are the main differences between VLOOKUP and XLOOKUP?

VLOOKUP XLOOKUP
Lookup direction Right only Left or right
Default match Approximate unless you pass FALSE Exact
Return column Number you count by hand A range you select
Not found #N/A, wrapped in IFERROR Built-in if_not_found argument
Insert a column in the table The index number may now point at the wrong column Ranges adjust automatically
Search from the bottom No search_mode of -1
Wildcards Work in exact-match mode Only with match_mode 2
Return several columns One column per formula One formula can return a range

The second row is the source of silent errors. A VLOOKUP without the last argument uses approximate matching, which assumes sorted data and can return a wrong row instead of an error. Always pass FALSE (or 0).

The fourth row shows a break. If you insert a column between Product and Price, the table range grows to A2:D4, but the 3 still means the third column, which is now the new one. This is a common cause of wrong values, and #REF! appears when the index exceeds the table width (see the #REF! error).

Which Excel and LibreOffice versions support XLOOKUP?

  • XLOOKUP: Excel 2021 and later, Excel for Microsoft 365, Excel for the web, Google Sheets, and LibreOffice Calc 24.8 and later.
  • VLOOKUP: every version of Excel, Google Sheets, LibreOffice and Numbers.

Excel 2019, 2016 and older do not have XLOOKUP. A workbook that uses it opens there with #NAME? errors, and the formula shows as _xlfn.XLOOKUP. The same happens in LibreOffice before 24.8.

Which lookup function should I use for each kind of file?

  • Files only you use, or a team on Microsoft 365: XLOOKUP.
  • Files sent to clients, auditors or readers whose software you do not control: VLOOKUP with FALSE, or INDEX/MATCH, which works everywhere:
=INDEX(C2:C4, MATCH("A-102", A2:A4, 0))
  • Lookups where the key is not in the leftmost column: XLOOKUP or INDEX/MATCH.

What causes #N/A or wrong matches in lookup formulas?

  • Text that looks identical may not match. Trailing spaces, a number stored as text, or different capitalization of codes can cause #N/A. Use TRIM or VALUE to clean the key.
  • Duplicate keys: both functions return the first match, unless you set XLOOKUP's search_mode to -1.
  • Lookup tables from datasets work well with codes. For example, you can join the GDP by country table to the world countries table by country code instead of by name, since names vary between sources.