FormuladrillXLOOKUP
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) finds a value in one column or row and returns the matching value from another, in any direction, with an optional value to show when nothing is found. It defaults to exact match and replaces VLOOKUP and INDEX/MATCH for most lookups.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Part | What it is |
|---|---|
lookup_value | The value to find. |
lookup_array | The single column or row to search. |
return_array | The column or row (or several columns) to return from. Same height or width as lookup_array. |
if_not_found | What to return when there is no match. Without it you get #N/A. |
match_mode | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. |
search_mode | 1 first to last (default), -1 last to first, 2 and -2 binary search on sorted data. |
Examples
=XLOOKUP("P-102", A2:A50, C2:C50)Result: The value in C on the row where A equals P-102
Exact match by default, no column number to count.
=XLOOKUP(E2, B2:B50, A2:A50, "Not found")Result: The value from column A, to the left of the lookup column, or Not found
Looking left needs no trick, just point return_array at the left column.
=XLOOKUP(45000, A2:A6, B2:B6, , -1)Result: The value next to 40000 when A2:A6 holds 0, 10000, 20000, 40000, 80000
match_mode -1 returns the next smaller value when there is no exact match, the bracket lookup VLOOKUP TRUE used to do.
=XLOOKUP(E2, A:A, B:B, "", 0, -1)Result: The value from B for the last row where A equals E2
search_mode -1 searches from the bottom up, so you get the most recent entry.
Common mistakes
- #VALUE! appears when lookup_array and return_array are different sizes, for example A2:A50 against B2:B60. Make them the same length.
- #NAME? means your Excel version does not have XLOOKUP (it needs Excel 2021 or Microsoft 365). Use INDEX/MATCH instead.
- An approximate lookup returns the wrong bracket because match_mode was left at 0 (exact). Set it to -1 for next smaller or 1 for next larger.
- Returning several columns works (return_array can be B2:D50) but the result spills; make sure the cells next to the formula are empty.
Practice XLOOKUP
These drills check your answer on live data, free, no account needed.
- When the code does not existReturn the stock level for the code in F1. If the code is not in the list, the cell must show the text Unknown code instead of an error.
- Return from the leftReturn the phone extension of the employee named in F1, using XLOOKUP. Answer in F2.
Questions
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP can return from any column (left or right), defaults to an exact match, takes a not-found value directly and does not break when columns are inserted. VLOOKUP only returns from columns to the right of the lookup column and needs a column number.
How do I return multiple columns with XLOOKUP?
Give a multi-column return_array, for example =XLOOKUP(E2, A2:A50, B2:D50). The result spills into three cells to the right.
Can XLOOKUP match two criteria?
Yes, join the criteria: =XLOOKUP(1, (A2:A50=E2)*(B2:B50=F2), C2:C50). The multiplication gives 1 where both are true, and XLOOKUP finds the first 1.
Related
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.
- INDEX MATCHHow to combine INDEX and MATCH in Excel to look up a value from any column, left or right, with worked examples, a two-way lookup and the usual errors.
- FILTERHow the Excel FILTER function returns all the rows of a range that meet a condition, with examples for one criterion, AND and OR, a not found value and SORT.
- MATCHHow the Excel MATCH function returns the position of a value in a row or column, with exact and approximate match types, examples and the causes of #N/A.