sheets.org
Excel

XLOOKUP vs VLOOKUP vs INDEX/MATCH: Which Should You Use?

Every spreadsheet user eventually needs to pull a value from one table into another. For twenty years the answer was VLOOKUP. Then INDEX/MATCH became the power-user favorite. Now XLOOKUP, available in both Excel and Google Sheets, does what both of them do with fewer sharp edges. Here is how the three compare and when to reach for each.

VLOOKUP: the classic, with well-known limits

=VLOOKUP(search_key, range, index, [is_sorted])
=VLOOKUP(A2, Products!A:D, 3, FALSE)

VLOOKUP searches the first column of a range and returns a value from a column you specify by number. It works, but three limitations bite constantly:

  • It can only look to the right — the search column must be the first column of your range.
  • The column index is a hard-coded number, so inserting a column silently breaks your formula.
  • Forgetting the fourth argument (FALSE for exact match) is the classic source of quietly wrong results.

INDEX/MATCH: flexible, but a mouthful

=INDEX(return_range, MATCH(search_key, search_range, 0))
=INDEX(Products!C:C, MATCH(A2, Products!B:B, 0))

INDEX/MATCH separates the "where to look" from the "what to return." That means it can look left, it survives inserted columns, and it is faster on huge datasets because it only touches two columns. The cost is readability — nesting two functions makes formulas harder for teammates to follow.

XLOOKUP: the modern default

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
=XLOOKUP(A2, Products!B:B, Products!C:C, "Not found")

XLOOKUP takes the flexibility of INDEX/MATCH and wraps it in one readable function. It looks in any direction, defaults to exact match (finally!), and has a built-in "not found" argument so you can retire your IFERROR wrappers. It can even search from the bottom up to find the last matching entry.

Which should you use?

  • Starting fresh? Use XLOOKUP. It is available in Google Sheets, Excel 365, and Excel 2021+.
  • Sharing files with older Excel versions? Excel 2019 and earlier do not support XLOOKUP — use INDEX/MATCH for compatibility.
  • Maintaining old sheets? Leave working VLOOKUPs alone, but write new lookups with XLOOKUP.

One habit upgrade pays for itself immediately: whatever function you choose, always demand an exact match. Approximate-match defaults have corrupted more reports than any other spreadsheet mistake.