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 passFALSE.- 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_modeandsearch_modesupport wildcards, next-larger matches and last-match searches.
If you must support older Excel, use INDEX/MATCH.