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
- Excel Template to Pay Off Debt? Yes, Here’s How
- Excel Inventory Template: How to Track Stock
- Excel Template Job Schedule: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Timesheet Formula With Breaks and Overnight ShiftsWith a shift start in B2, shift end in C2, break start in D2, and break end in…
- Excel Formula for Time Between DatesTo find the time between two dates in Excel, subtract the earlier date or date-time from the later…
- Excel NPV Formula Year by Year: How It WorksIn Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of…
- Excel Timesheet Formula Minus Lunch: Simple ExamplesTo subtract an unpaid lunch entered as minutes, put the shift start in B2, the end in C2,…
- COUNTA and COUNTIF in Excel: How to Use Them TogetherTo find what share of filled status cells say Complete, with statuses in B2:B11, use =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). If five…
- Text After an Excel Formula: Add Labels and Sheet NamesTo put text after an Excel formula, join the formula result to a quoted text string with an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.