VLOOKUP, XLOOKUP or INDEX+MATCH: which one should I use?

Looking up a value in another table is one of the most common things in Excel: a product's price, a scholarship amount, a customer's name. There are three ways to do it and they're not equally comfortable.

VLOOKUP: the classic

=VLOOKUP(B2,$G$2:$H$10,2,FALSE)

It looks for B2 in the first column of the range and returns column 2. It works in any version, but it has two drawbacks: it only looks to the right, and the column number breaks if you insert columns.

🦉 Tip: Don't forget the final FALSE: without it, VLOOKUP searches for an approximate match and can return wrong data without warning.

XLOOKUP: the modern one

=XLOOKUP(B2,$G$2:$G$10,$H$2:$H$10,"Not found")

You give it the column to search in and the column to return, separately. It searches in any direction, is exact by default and lets you show a message if nothing is found. If your Excel has it, use it.

INDEX + MATCH: the all-rounder

=INDEX($H$2:$H$10,MATCH(B2,$G$2:$G$10,0))

MATCH tells you at which position the value is; INDEX returns what's in that position of another column. It's flexible and works in older versions.

• •Recent Excel → XLOOKUP.
• •Sharing the file with old versions → INDEX + MATCH.
• •Small, fixed table → VLOOKUP with FALSE is fine.