XLOOKUP has been around for years but most people still reach for VLOOKUP by habit. Here's the honest breakdown — and when INDEX MATCH beats both.
If you've used Excel for more than a week, you've probably heard of VLOOKUP. It's the formula most people learn first for looking things up in a table. But in 2019, Microsoft released XLOOKUP — a newer, smarter version that fixes almost every frustration with VLOOKUP.
So should you switch? Should you forget VLOOKUP entirely? Let's settle this properly.
VLOOKUP looks down the first column of a table, finds a match, and returns a value from another column in the same row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: find the price for "Coffee" in a product list.
=VLOOKUP("Coffee", A2:C10, 3, 0)
← looks for Coffee in column A, returns column 3
The 0 at the end means exact match. Always use 0 unless you specifically need approximate matching.
Here's what nobody tells beginners: VLOOKUP has a fundamental limitation that has caused spreadsheet errors for decades.
It can only look right. The thing you're looking for must always be in the leftmost column of your table. The value you want to return must be to the right of it. You cannot return something to the left.
If you rearrange your columns — even just swapping two columns around — your VLOOKUP breaks and returns wrong answers silently. No error, just wrong data.
The other issue: the column number is hardcoded. =VLOOKUP("Coffee", A2:C10, 3, 0) returns column 3. If someone inserts a new column in your table, suddenly column 3 is now different data — and again, no error, just wrong answers.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The same search — find the price for "Coffee":
=XLOOKUP("Coffee", A2:A10, C2:C10)
← look for Coffee in A, return the matching value from C
Notice what's different: you specify the return column directly as a range, not as a number. That means inserting columns never breaks it. And you can return columns to the left, right, above — anywhere.
XLOOKUP also has a built-in fallback for when nothing is found:
=XLOOKUP("Coffee", A2:A10, C2:C10, "Not found")
← shows "Not found" instead of #N/A error
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Looks left as well as right | ✗ No | ✓ Yes |
| Survives column insertion | ✗ No | ✓ Yes |
| Built-in "not found" message | ✗ No | ✓ Yes |
| Returns multiple columns at once | ✗ No | ✓ Yes |
| Works in older Excel versions | ✓ Yes | ✗ 2019+ only |
| Easier to read and understand | ✗ Harder | ✓ Easier |
INDEX MATCH is the combination of two separate formulas — INDEX returns a value at a position, MATCH finds the position. Together they create the most flexible lookup in Excel.
=INDEX(C2:C10, MATCH("Coffee", A2:A10, 0))
INDEX MATCH wins when you need to:
INDEX MATCH is more powerful but harder to read. If XLOOKUP can do what you need, use XLOOKUP. Only reach for INDEX MATCH when XLOOKUP falls short.
If you're learning Excel in 2026, start with XLOOKUP. It does everything VLOOKUP does, plus more, with a cleaner syntax. Only learn VLOOKUP so you can understand it when you see it in other people's spreadsheets.
ExcelPro has exercises for VLOOKUP, XLOOKUP, and INDEX MATCH. Type real formulas, get instant feedback.
Start practising free →