XLOOKUP step by step, in Excel and Google Sheets

By XcelFormula Editorial Team · Published 20 October 2026

Learn XLOOKUP step by step with worked examples: exact matches, not-found messages, looking left and returning several columns.

XLOOKUP replaces VLOOKUP and HLOOKUP with one clearer function. It is available in Microsoft 365, Excel 2021 and later, and Google Sheets. Here is how it works.

The syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Only the first three arguments are required.

Step 1: a basic lookup

Say product codes are in A2:A100 and prices in C2:C100. To find the price of the code in F2:

=XLOOKUP(F2, A2:A100, C2:C100)

Unlike VLOOKUP, you do not count columns, and the match is exact by default.

Step 2: a friendly "not found"

=XLOOKUP(F2, A2:A100, C2:C100, "No such code")

This replaces the #N/A error with your own message, without needing IFERROR.

Step 3: looking to the left

Because the lookup and return ranges are separate, the return column can sit to the left of the lookup column. With names in D and IDs in B:

=XLOOKUP("Patel", D2:D100, B2:B100)

Step 4: return several columns at once

=XLOOKUP(F2, A2:A100, B2:D100) spills the matching row's values from B to D into neighbouring cells.

Step 5: approximate matches

For tax bands or price breaks, set the match mode to -1 (exact or next smaller) or 1 (exact or next larger). For example, to find a discount rate from a thresholds table:

=XLOOKUP(G2, Thresholds, Rates, , -1)

Step 6: search from the bottom

Set the search mode to -1 to find the last match, such as the most recent order for a customer.

Common mistakes

• Lookup and return ranges of different sizes give a #VALUE! error.
• Extra spaces in the data stop exact matches. Clean them with TRIM.
• Numbers stored as text will not match real numbers.

Step 7: wildcard matches

Set the match mode to 2 to use wildcards: an asterisk (*) for any characters and a question mark (?) for one character. To find the first product whose name contains "bottle":

=XLOOKUP("*bottle*", B2:B100, C2:C100, "Not found", 2)

Step 8: lookups with two conditions

To find a value that matches two criteria, such as a product and a region, multiply two tests together and look up the number 1:

=XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100, "Not found")

Each test returns TRUE or FALSE; multiplying them gives 1 only where both are true. This works in Excel 365. In Google Sheets, if it doesn't calculate, wrap the formula in ARRAYFORMULA.

Each test returns TRUE or FALSE; multiplying them gives 1 only where both are true.

Step 9: a two-way lookup

You can nest XLOOKUPs to find a value at the intersection of a row and a column, such as sales for a given month and region:

=XLOOKUP(F2, A2:A13, XLOOKUP(G2, B1:E1, B2:E13))

The inner XLOOKUP returns the whole column for the chosen region; the outer one picks the row for the month.

XLOOKUP, VLOOKUP or INDEX and MATCH?

• XLOOKUP is the clearest choice if everyone uses Microsoft 365, Excel 2021 or later, or Google Sheets.
• Compatibility: if a file will be opened in Excel 2019 or earlier, XLOOKUP formulas show a #NAME? error. Use INDEX and MATCH instead, which work in every version and can also look left.
• VLOOKUP still works, but it defaults to an approximate match unless you add FALSE as the last argument, a common cause of wrong results.

A note on fast search modes

Search modes 2 and -2 use a binary search, which is faster on very large data but only works correctly if the lookup column is sorted. If it isn't, results can be silently wrong. For most sheets, the default mode is fine.

Practise

Build a small table of ten products with codes, names, categories and prices, then try each step above. Seeing results change as you edit the lookup value is the quickest way to learn.

Sources and further reading

• Microsoft Support: VLOOKUP function
• Microsoft Support: XLOOKUP function
• Google Docs Editors Help: XLOOKUP

Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.