Using XLOOKUP in Excel: a practical, step-by-step guide

XLOOKUP finds a value in one range and returns a corresponding value from another range using =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Use the worked example and checklist in this article to build, test, and troubleshoot your own XLOOKUP formulas.

What XLOOKUP does and the core syntax

XLOOKUP replaces many older lookup patterns by allowing a lookup range and a separate return range, optional custom not-found text, and flexible matching and search order. The full syntax is:

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

lookup_value - the value you want to find.
lookup_array - the range to search for the lookup_value.
return_array - the range from which to return a matching value.
[if_not_found] - optional text or value to return instead of an error when nothing matches.
[match_mode] - optional parameter that controls exact, approximate, or wildcard matching.
[search_mode] - optional parameter that controls search direction and algorithm.

Quick reference for match_mode and search_mode

match_mode: 0 = exact match (default); -1 = exact match or next smaller; 1 = exact match or next larger; 2 = wildcard match using * ? ~.
search_mode: 1 = search first-to-last (default); -1 = search last-to-first; 2 and -2 are binary searches that require sorted data and are less commonly used.

Step-by-step worked example

Below is a concise step-by-step example that shows how to write a formula, test it, and adapt it to a cross-sheet lookup.

Prepare the data. On Sheet1 have a table: column A SKU, column B Quantity. On Sheet2 have SKU in column A and Price in column B.
Identify the goal. Suppose on Sheet1 you want to pull Price from Sheet2 next to each SKU on Sheet1.
Write the basic formula on Sheet1 cell C2 (next to the first SKU):
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)
This finds the SKU in Sheet2 column A and returns the corresponding Price from column B.
Handle missing SKUs. Add an if_not_found value to avoid #N/A:
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Price not found")
Lock ranges for copying. If you will fill the formula down, use full column references or anchored ranges:
=XLOOKUP(A2, Sheet2!$A:$A
000, Sheet2!$B:$B
000, "Price not found")
Test exact vs approximate. If SKUs are text use exact match (default). For numeric nearest-match use match_mode 1 or -1 and ensure the lookup_array is sorted appropriately:
=XLOOKUP(B2, Rates!$A:$A$500, Rates!$B:$B$500, "No rate", 1)
Copy the formula down. Verify a handful of rows manually to confirm results, especially boundary conditions like first and last entries.

Exact vs approximate match: when to use each

Choose match_mode based on the data and desired behavior:

Use exact match (match_mode 0 or omit) for text keys such as SKUs, names, or IDs.
Use approximate match (match_mode 1 or -1) for numeric brackets or rate tables where you want the nearest value, and only when the lookup_array is sorted appropriately.
Use wildcard match (match_mode 2) when lookup_value should match patterns like "ABC*" or "Smith?" and the lookup_array contains text.

When using binary search options (search_mode 2 or -2), the lookup_array must be sorted to guarantee correct results; otherwise rely on the default first-to-last search.

Error handling: practical options

Two common ways to keep formulas user-friendly are the if_not_found argument and wrapping XLOOKUP with IFERROR. The if_not_found argument lets the formula return a custom message without producing an error. For example:

=XLOOKUP(E2, Products!A:A, Products!C:C, "Not listed")

If you prefer a broader trap that covers other runtime errors, wrap the lookup with IFERROR. See the detailed guidance in Handle Lookup Errors. Example:

=IFERROR(XLOOKUP(E2, Products!A:A, Products!C:C), "Lookup failed")

Common mistakes and quick fixes

These are frequent issues users encounter and how to fix them.

#N/A or Not found - The lookup_value does not appear in lookup_array. Fix: confirm spelling, remove extra spaces, or add an if_not_found message.
Wrong return values - Mismatched ranges. Fix: ensure lookup_array and return_array have the same number of rows and align correctly.
Approximate returns incorrect - Using approximate match on unsorted data. Fix: sort the lookup_array or use exact match.
Cross-sheet reference errors - Typo in sheet name. Fix: check sheet name and use single quotes for names with spaces: 'Price Sheet'!A:A.
Performance issues on large ranges - Using entire column references with many volatile formulas can slow the workbook. Fix: use precise ranges or structured tables.

Practical checklist before you finalize

Are lookup_array and return_array the same size and aligned?
Do you need exact, wildcard, or approximate matching?
Have you added an if_not_found message or wrapped with IFERROR?
For cross-sheet lookups, are the sheet names and ranges correct and anchored as needed?
Have you tested edge cases like missing values, duplicates, and first/last entries?

If you are transitioning from older lookup functions, the linked guide on How VLOOKUP Works explains when XLOOKUP is a direct replacement and when alternatives still make sense. For multi-condition lookups, see Advanced Lookups for patterns and examples.

Summary and next steps

XLOOKUP simplifies many common lookup scenarios by separating the lookup range from the return range and by adding built-in not-found handling and flexible matching. Start by building a single working formula, add if_not_found or IFERROR to handle missing values gracefully, and copy it down with anchored ranges. Use the checklist above to avoid common pitfalls and test a few edge cases before rolling the formula into production.

Follow the worked example, keep a short test dataset handy, and the function will become a reliable tool in your spreadsheet workflow.