XLOOKUP in Excel: Syntax, Examples and Common Errors

Direct answer: XLOOKUP finds a value in one range and returns the matching item from another range: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It replaces most VLOOKUP and INDEX MATCH uses.

Basic example

You have product IDs in column A and prices in column C of a price list. To get the price for the ID in cell F2: =XLOOKUP(F2, A:A, C:C, "Not found"). The last argument replaces the #N/A error with a readable message.

Why it improves on VLOOKUP

• No column index number to break when a column is inserted
• Can return values from the left of the lookup column
• Exact match by default
• Built-in “not found” result

Approximate match for tiers

For commission tiers, set the match mode to -1 (exact or next smaller): =XLOOKUP(B2, Tiers[From], Tiers[Rate], 0, -1). The table must hold the lower bounds of each band.

Returning several columns

Give a multi-column return range and the result spills: =XLOOKUP(F2, A:A, C:E) returns price, stock and supplier in one formula (Microsoft 365 / Excel 2021+).

Common mistakes

• #N/A on a value that looks present: a trailing space or a number stored as text. Use TRIM or VALUE on one side.
• Mismatched range sizes: lookup and return arrays must have the same number of rows.
• Whole-column references on huge files: use an Excel Table so ranges stay tight.

Practical steps

1. Convert both lists to Tables.
2. Write the XLOOKUP with the not-found argument.
3. Test with a value you know is missing.
4. Document the formula in a note cell.

Related concepts

For two-way lookups combine two XLOOKUPs; for older versions see INDEX MATCH. When your lookups span many files, move the join into Power Query.

Conclusion

If you have Microsoft 365, default to XLOOKUP. Practise it in the Advanced Excel course, where lookups are taught with realistic broken data.

Frequently asked questions