XLOOKUP Return Value: How It Works and Examples

Formulas & functions · Updated November 22, 2026

XLOOKUP returns the matching value from the column or row you specify, based on a lookup value you provide. For example, =XLOOKUP("A-100", A2:A10, B2:B10) finds “A-100” in cells A2:A10 and returns the corresponding value from B2:B10.

What XLOOKUP Actually Returns

XLOOKUP is a lookup and reference function introduced in Excel 2021 and Microsoft 365. It searches a lookup array (the range you search in) for a lookup value, then returns a value from a return array (the range you want to pull from). The return array can be the same size as the lookup array, and it can be to the right, left, above, or below the lookup array—unlike VLOOKUP, which only looks to the right.

The function returns a single value by default. If your lookup value appears multiple times, XLOOKUP returns the first match unless you set the match mode argument to return the next smaller, next larger, or an exact match with wildcards. If no match is found, it returns the value you supply in the if_not_found argument, or #N/A if you omit it.

XLOOKUP Syntax and Arguments

The full syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value – the value to search for (required).
  • lookup_array – the range to search in (required).
  • return_array – the range to return a value from (required).
  • if_not_found – optional text or value to show when no match exists.
  • match_mode – optional: 0 (exact match, default), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard match).
  • search_mode – optional: 1 (first to last), -1 (last to first), 2 (binary ascending), -2 (binary descending).

Example: with employee IDs in A2:A20 and names in B2:B20, =XLOOKUP("E104", A2:A20, B2:B20, "Not found") returns the name for ID E104, or “Not found” if it doesn’t exist.

How to Choose the Return Array

The return array must have the same number of rows (or columns) as the lookup array, but it can be anywhere in the worksheet. For a horizontal lookup, both arrays must have the same number of columns. For a vertical lookup, both must have the same number of rows.

Common patterns:

  • Return a single column: =XLOOKUP(A2, Products!A:A, Products!C:C)
  • Return multiple columns: =XLOOKUP(A2, Products!A:A, Products!B:D) – this returns a spilled range of three columns (requires Excel 365).
  • Return a value from a row above the lookup: =XLOOKUP("Q1", A1:D1, A2:D2) – works because the return array can be a different row.

XLOOKUP vs VLOOKUP and INDEX/MATCH

VLOOKUP can only return values to the right of the lookup column, and it breaks if you insert a column in the table. XLOOKUP defaults to exact match, while VLOOKUP defaults to approximate. INDEX/MATCH is more flexible than VLOOKUP but requires two functions and a more complex formula. XLOOKUP does the same job with one function and clearer syntax.

Example comparison: to find price for product “P-45” in a table with product in A, price in C:

  • VLOOKUP: =VLOOKUP("P-45", A:C, 3, FALSE)
  • INDEX/MATCH: =INDEX(C:C, MATCH("P-45", A:A, 0))
  • XLOOKUP: =XLOOKUP("P-45", A:A, C:C)

Common Problems and How to Fix Them

#N/A error – no match found. Add the if_not_found argument to show a friendly message: =XLOOKUP(A2, B:B, C:C, "No match").

#VALUE! error – the lookup and return arrays have different sizes. Check that both have the same number of rows or columns.

Wrong value returned – if you expect exact match but get a close value, you may have omitted the match_mode and the data is not sorted. Always use 0 for exact match unless you need approximate.

Spill error – when returning multiple columns, ensure the adjacent cells are empty, or use @ to return only the first value (though that defeats the purpose).

Using XLOOKUP with Wildcards and Approximate Matches

Set match_mode to 2 to use wildcards: * matches any sequence, ? matches any single character. Example: =XLOOKUP("A*", A2:A10, B2:B10, , 2) returns the first value where column A starts with “A”.

For approximate matches (like finding a tax rate based on income brackets), set match_mode to -1 (exact or next smaller) or 1 (exact or next larger). The lookup array must be sorted in ascending order for -1 or descending for 1.

XLOOKUP Availability and Alternatives

XLOOKUP works in Excel 2021 and Microsoft 365 on Windows, Mac, and Excel for the web. It is not available in Excel 2019 or earlier. If you use an older version, use VLOOKUP or INDEX/MATCH instead.

For dynamic arrays, XLOOKUP can return multiple values that spill into adjacent cells. In Excel for the web, the behavior is identical, but the if_not_found argument must be a constant—it cannot be a range reference.

Frequently Asked Questions

Can XLOOKUP return multiple values at once? Yes, if you specify a return array with multiple columns or rows, XLOOKUP returns a spilled array. For example, =XLOOKUP(A2, A:A, B:C) returns two adjacent cells. This requires Excel 365 or Excel 2021 with dynamic arrays enabled.

What does XLOOKUP return if no match is found? By default it returns #N/A. You can change that with the fourth argument: =XLOOKUP(A2, B:B, C:C, "Not found").

Is XLOOKUP case-sensitive? No, by default it is not. To make it case-sensitive, use XLOOKUP with the EXACT function in an array formula, or use MATCH with EXACT. For example: =XLOOKUP(TRUE, EXACT(A2, B2:B10), C2:C10) entered with Ctrl+Shift+Enter in older versions.

Can XLOOKUP search from bottom to top? Yes, set the search_mode argument to -1. Example: =XLOOKUP("X", A:A, B:B, , 0, -1) returns the last occurrence.

Why is my XLOOKUP returning a wrong value? Check that the lookup_array and return_array are aligned correctly. A common mistake is using different row counts, or forgetting that the lookup_array must be sorted if you use approximate match modes.

Does XLOOKUP work in Google Sheets? No, Google Sheets has its own XLOOKUP function that works similarly, but the syntax and some behaviors (like wildcards) differ. In Excel, XLOOKUP is only available in Excel 2021 and Microsoft 365.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.