What is the difference between VLOOKUP, INDEX-MATCH and XLOOKUP?

All three retrieve a value from a table; they differ in flexibility and robustness.

VLOOKUP — =VLOOKUP(value, table, col_index, FALSE). Its limitations are why the others exist:

• It can only look rightwards — the lookup column must be leftmost.
• The column index is a hard-coded number, so inserting a column silently returns the wrong data. This is the dangerous one, because nothing errors.
• Always pass FALSE for exact match; omitting it defaults to approximate and returns wrong answers on unsorted data.

INDEX-MATCH — =INDEX(return_range, MATCH(value, lookup_range, 0)). INDEX returns a value at a position; MATCH finds the position. Together they look in any direction, break nothing when columns move, and are faster on large datasets because only two columns are referenced.

XLOOKUP — =XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The modern answer: searches any direction, defaults to exact match, has built-in not-found handling instead of wrapping in IFERROR, can search from the bottom up, and can return an entire row or column.

Note: Use XLOOKUP where available, INDEX-MATCH for compatibility with older versions. The compatibility point matters — XLOOKUP requires Microsoft 365 or Excel 2021 and breaks for anyone on an older build.