XLOOKUP is the formula Microsoft built to replace VLOOKUP, HLOOKUP, and INDEX MATCH. It's simpler, more powerful, and handles errors gracefully. Here's everything you need.
XLOOKUP arrived in Excel 2019 (and Microsoft 365) as the official replacement for VLOOKUP. It addresses every major complaint people had about VLOOKUP:
If you're on Excel 2019 or Microsoft 365, you should be using XLOOKUP.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
For most lookups, you only need the first three arguments. The rest are for advanced use cases.
You have a product list and want to find the price for a specific product.
=XLOOKUP("Coffee", A2:A10, B2:B10)
← looks for Coffee in column A, returns the price from column B
Reference a cell instead of typing the value — change the cell and the result updates automatically.
=XLOOKUP(D2, A2:A10, B2:B10)
← D2 contains the product name to search for
=XLOOKUP(D2, A2:A10, B2:B10, "Not found")
← shows "Not found" instead of a #N/A error
Your ID numbers are in column C, but the names you want to return are in column A — to the left.
=XLOOKUP(F2, C2:C10, A2:A10, "Not found")
← searches column C, returns from column A (left of C)
XLOOKUP can return a whole range of columns in one formula.
=XLOOKUP(D2, A2:A10, B2:C10)
← returns both column B AND column C for the matched row
Find entries that contain a word, not exact matches.
=XLOOKUP("*coffee*", A2:A10, B2:B10, "Not found", 2)
← match_mode 2 enables wildcards. * matches any characters.
If a value appears multiple times, return the last occurrence.
=XLOOKUP(D2, A2:A10, B2:B10, "Not found", 0, -1)
← search_mode -1 searches from last to first
Nest two XLOOKUPs to find values at the intersection of a row and column — like reading a table.
=XLOOKUP(G2, A2:A10, XLOOKUP(H2, B1:F1, B2:F10))
← G2 = row to find, H2 = column to find
The built-in fourth argument is cleaner than wrapping in IFERROR — but both work:
Option 1 — built-in (cleaner)
=XLOOKUP(D2, A:A, B:B, "Not found")
Option 2 — IFERROR wrapper
=IFERROR(XLOOKUP(D2, A:A, B:B), "Not found")
If you're sharing files with people on older Excel versions, they'll see a #NAME? error. Use VLOOKUP or INDEX MATCH for maximum compatibility.
ExcelPro has XLOOKUP exercises across all 8 tracks — in real scenarios with real data. Free to start.
Try XLOOKUP exercises →