VLOOKUP vs. XLOOKUP: Which Should You Use?

VLOOKUP: the classic

VLOOKUP searches down the first column of a table for a value, then returns something from a column to the right of it:

The last argument, FALSE, means "exact match" — you almost always want this. Leaving it off (or setting it TRUE) tells Excel to find an approximate match, which silently returns the wrong row if your data isn't sorted.

XLOOKUP: the modern replacement

XLOOKUP fixes both problems. It takes the lookup range and the return range as two separate arguments, so it can look in either direction, and inserting columns doesn't break it:

XLOOKUP also defaults to an exact match (no easy-to-forget FALSE argument needed), and returns a clear #N/A — or a custom fallback you specify — when nothing matches, instead of VLOOKUP's less obvious error behavior.

HLOOKUP and INDEX/MATCH

HLOOKUP is VLOOKUP's horizontal sibling — it searches across the first row of a table instead of down the first column, for data laid out sideways. For lookups that need to go left instead of right without XLOOKUP available, the classic workaround is INDEX combined with MATCH — more verbose, but as flexible as XLOOKUP.

Which one should you use?

If XLOOKUP is available in your version of Excel, use it by default — it's strictly more capable. VLOOKUP is still worth knowing well, though: it's what you'll see in most existing spreadsheets, job interviews, and older shared files.