What XLOOKUP Does
XLOOKUP searches a column for a value and returns a corresponding value from another column — in any direction. It replaces VLOOKUP and HLOOKUP with a single, more flexible function. It also has built-in handling for missing values, so you don't need to wrap it in IFERROR.
Syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
| Parameter | Description |
|---|---|
| search_key | The value to search for. |
| lookup_range | The row or column to search. |
| result_range | The row or column from which to return the result. |
| missing_value | Value to return if no match is found. Defaults to #N/A. |
| match_mode | 0 = exact (default), 1 = next larger, -1 = next smaller, 2 = wildcard. |
| search_mode | 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending. |
Basic Examples
Example 1: Look Up a Customer Email
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | |
| 2 | C01 | Lena | lena@acme.com |
| 3 | C02 | Marco | marco@globex.com |
=XLOOKUP("C02", A2:A3, C2:C3)
Result: marco@globex.com
Example 2: Default Value When Not Found
=XLOOKUP("C99", A2:A3, C2:C3, "Not found")
Result: Not found — no IFERROR needed.
Example 3: Return Multiple Columns
XLOOKUP can return an entire row of results if the result range spans multiple columns:
=XLOOKUP("C01", A2:A3, B2:C3)
Result: Lena | lena@acme.com (spills into two cells)
Advanced Examples
Reverse Search — Last Match
Find the most recent order for a customer by searching from bottom to top:
=XLOOKUP("Lena", B2:B500, D2:D500, "No orders", 0, -1)
Setting search_mode to -1 starts from the last row, so it returns the latest entry for "Lena."
Approximate Match — Tax Bracket
Given income brackets in ascending order, find the applicable tax rate:
| A | B | |
|---|---|---|
| 1 | Income | Rate |
| 2 | 0 | 10% |
| 3 | 40000 | 22% |
| 4 | 85000 | 32% |
=XLOOKUP(55000, A2:A4, B2:B4, , -1)
Result: 22% — match_mode -1 finds the largest value less than or equal to 55,000.
Common Mistakes
- Using XLOOKUP in older sheets shared with Excel users. XLOOKUP is supported in Google Sheets and Excel 365, but not in Excel 2019 or earlier. If collaborators use older versions, the formula will break on their end.
- Mixing up match_mode and search_mode. Match_mode controls what kind of match (exact, next larger, wildcard). Search_mode controls direction (first-to-last, last-to-first). Confusing the two leads to unexpected results.
- Lookup and result ranges of different sizes. Both ranges must have the same number of rows (or columns for horizontal lookups). A mismatch returns an error.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "look up the email for customer C02" — and the formula is generated for you. Install SheetAI to try it free.