XLOOKUP Function in Google Sheets
Modern replacement for VLOOKUP, HLOOKUP, and INDEX/MATCH. Searches a range or array for a match and returns the corresponding item from a second range or array. Handles "not found" inline, can search in any direction, and defaults to exact match.
Syntax
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])Returns: The value from result_range in the same position as the match in lookup_range.
Excel equivalent: XLOOKUP (identical, requires Excel 2021+ or Microsoft 365)
Introduced: 2022
Parameters
| Name | Required | Description |
|---|---|---|
| search_key | Required | The value to search for. |
| lookup_range | Required | The single row or column to search in. |
| result_range | Required | The single row or column to return a value from. |
| missing_value | Optional | What to return if no match is found. Replaces the need for IFERROR. |
| match_mode | Optional | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
| search_mode | Optional | 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending. |
Examples
Basic exact-match lookup
=XLOOKUP(A2, Products!A:A, Products!C:C)Look up the value in A2 in the Products sheet's column A, return the matching value from column C. No need for column index numbers — you pass the result column directly.
With built-in not-found message
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not in catalog")The fourth argument replaces the IFERROR wrapper that VLOOKUP needs. Cleaner formula, no nested call.
Look up the most recent match
=XLOOKUP("Acme", Orders!B:B, Orders!E:E, "No orders", 0, -1)search_mode = -1 searches from last to first, returning the most recent matching row — useful for "last order date" or "most recent status" lookups.
When to use an alternative
- VLOOKUP — You need compatibility with very old sheets or Excel versions before 2019.
- INDEX + MATCH — You need to do something XLOOKUP can't quite handle, like a 2D lookup with MATCH on both axes.
- FILTER — You need to return multiple matching rows, not just the first or last.
Common errors and how to fix them
#N/A
Cause: No match found and no missing_value provided.
Fix: Add a fourth argument with your fallback value or text.
#VALUE!
Cause: lookup_range and result_range have different dimensions.
Fix: Both must be the same length. If you select A1:A100 for lookup, result must also be 100 cells.
Returns first match when you wanted last
Cause: Default search_mode is 1 (first to last).
Fix: Pass -1 as the sixth argument to search from bottom to top.
Related functions
Frequently Asked Questions
Is XLOOKUP available in Google Sheets?
Yes. Google Sheets added XLOOKUP in 2022 and it's available to all users in any Google Workspace tier and personal Google accounts. You don't need a premium subscription.
Should I use XLOOKUP or VLOOKUP?
Use XLOOKUP for any new formula. It's strictly more capable: searches in any direction, defaults to exact match (the safer default), handles not-found inline, and doesn't break when you insert columns. Keep VLOOKUP only when editing legacy sheets where consistency matters.
Can XLOOKUP return multiple values?
Not directly — XLOOKUP returns one value per call. To return multiple matches, use FILTER. To return multiple columns from a single match, pass a multi-column result_range and XLOOKUP returns the whole row as a spilled array.