SheetsdrillXLOOKUP
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) finds a value in one column or row and returns the value at the same position in another, in any direction, with a built-in if-not-found value. It is exact match by default and replaces VLOOKUP and INDEX/MATCH for most lookups.
Syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])| Part | What it is |
|---|---|
search_key | The value to find. |
lookup_range | The single column or row to search in. |
result_range | The column or row to return from, same size as lookup_range. It can be left of the lookup column. |
missing_value | Optional text or value shown when nothing matches, instead of #N/A. |
match_mode | 0 exact (default), 1 exact or next larger, -1 exact or next smaller, 2 wildcard. |
search_mode | 1 from the top (default), -1 from the bottom, 2 and -2 binary search on sorted data. |
Examples
=XLOOKUP(A2, Products!B:B, Products!A:A)Result: The SKU in column A for the product name in A2 (a look to the left)
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")Result: The price, or Not found
=XLOOKUP(B2, {0;1000;5000}, {"Low";"Mid";"High"}, , -1)Result: Mid for 2500
Match mode -1 picks the largest threshold not above B2.
Common mistakes
- #VALUE! or a wrong row because lookup_range and result_range have different sizes; use the same rows for both (A2:A100 with C2:C100).
- #N/A on a value that is present is usually a type mismatch (text versus number) or extra spaces; wrap in VALUE or TRIM.
- Leaving an argument out with nothing between commas is fine, but putting "" in match_mode gives an error; use 0 or leave it empty.
Practice XLOOKUP
These drills check your answer on live data, free, no account needed.
- Look left with XLOOKUPReturn the email address of the person named in F1. The names are in column C and the emails are to their left, so use XLOOKUP. Answer in F2.
- A friendly "Not found"Look up the discount for the code in E1. When the code does not exist, show the text "Not found" instead of an error. Answer in E2 with XLOOKUP.
Questions
Does XLOOKUP work in Google Sheets?
Yes, Google Sheets added XLOOKUP in 2022 with the same arguments as Excel.
How do I XLOOKUP with two conditions?
Concatenate the keys: =XLOOKUP(A2&B2, Data!A:A&Data!B:B, Data!C:C), wrapped in ARRAYFORMULA if needed, or use FILTER.
Related
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- INDEX and MATCHHow INDEX and MATCH work together in Google Sheets to look up values left or right, do two-way lookups by row and column, and find the last match.
- IFNAHow the IFNA function in Google Sheets replaces only the #N/A error from lookups, why it is safer than IFERROR, with examples for VLOOKUP and MATCH.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.