XLOOKUP finds a value in one row or column and returns the aligned result. It handles exact matches by default, can look left, and can return several columns with one formula.
For a basic exact lookup, use =XLOOKUP(search_key,lookup_range,result_range). The examples below also cover missing values, approximate tiers, and the last matching record.
XLOOKUP Function Syntax in Google Sheets
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])- search_key is the value to find.
- lookup_range is one row or column to search.
- result_range supplies the aligned answer and may include several columns.
- missing_value replaces the default
#N/Awhen nothing matches. - match_mode controls exact, approximate, or wildcard matching.
- search_mode controls search direction or binary search.
The lookup range and result range must align. If the lookup range has six rows, the result range must also have six rows.
When to Use XLOOKUP
- Retrieve a department, price, status, or other value by ID.
- Return a value from a column left of the lookup column.
- Show a clear message when no match exists.
- Assign a value to the nearest lower or higher threshold.
- Return a full record from one matching row.
Look Up an Exact Match
| Product code | Product | Price | Stock |
|---|---|---|---|
| P-101 | Paper | 12.50 | 48 |
| P-102 | Binder | 8.75 | 0 |
| P-103 | Marker | 3.25 | 120 |
| P-104 | Stapler | 15.00 | 25 |
| P-105 | Folder | 2.00 | 200 |
| P-101 | Paper | 12.50 | 60 |
This formula searches product codes in A2:A7 and returns the product from B2:B7:
=XLOOKUP("P-103",$A$2:$A$7,$B$2:$B$7)The tested result is Marker. XLOOKUP uses exact matching when match_mode is omitted.
Look to the Left With XLOOKUP
The result range may sit left of the lookup range. This formula searches product names in column B and returns the code from column A:
=XLOOKUP("Binder",$B$2:$B$7,$A$2:$A$7)The result is P-102. Unlike VLOOKUP, XLOOKUP does not require the result column to be right of the lookup column.
Show a Custom Message When There Is No Match
=XLOOKUP("P-999",$A$2:$A$7,$B$2:$B$7,"Not found")The fourth argument returns Not found. Without it, the same missing key returns #N/A.
Use an Approximate Match for Tiers
Suppose J2:J5 contains thresholds 0, 100, 250, and 500. K2:K5 contains None, Bronze, Silver, and Gold.
=XLOOKUP(320,$J$2:$J$5,$K$2:$K$5,,-1)The -1 match mode returns an exact match or the next smaller threshold. The tested result for 320 is Silver.
The empty fourth argument explains the two commas before -1. It skips missing_value so the formula can reach match_mode.
Return a Whole Row With One XLOOKUP
=XLOOKUP("P-104",$A$2:$A$7,$B$2:$D$7)The three-column result range returns Stapler, 15, and 25 across three cells.
Return the Last Matching Record
When a code appears more than once, search_mode -1 searches from bottom to top:
=XLOOKUP("P-101",$A$2:$A$7,$D$2:$D$7,,0,-1)The tested result is 60, the stock value from the last P-101 row. The 0 keeps exact matching while -1 reverses the search.
Google also offers binary search modes for large sorted ranges. Use them only when the lookup range is sorted exactly as the selected mode requires.
See Google’s XLOOKUP reference for every match and search mode.
Other Google Sheets articles you may also like