Skip to content

Lookups · Easy

What is the difference between VLOOKUP and XLOOKUP?

Short answer

VLOOKUP searches only the first column of a range and returns a column by index number, defaulting to approximate match. XLOOKUP takes separate lookup and return ranges, can look left, defaults to exact match, has a built-in 'not found' value and can search from the bottom. Prefer XLOOKUP where available.

Detailed answer

VLOOKUP pitfalls

  • =VLOOKUP(A2, B:D, 3) without the fourth argument uses approximate match — a classic source of silent errors. Always pass FALSE.
  • The column index breaks when someone inserts a column.
  • It cannot return values to the left of the key.

XLOOKUP

=XLOOKUP(A2, Products[id], Products[price], "Not found")

  • Exact match by default.
  • Return range can be anywhere.
  • match_mode and search_mode support wildcards, next-larger matches and last-match searches.

If you must support older Excel, use INDEX/MATCH.