XLOOKUP in Google Sheets, Exact Lookup
By Szabó Gergő · Updated
Sheets XLOOKUP can look left or right and can return a custom miss value. FILTER remains the habit when several rows can match.
Syntax and arguments
=XLOOKUP(search_key, lookup_range, result_range, [missing_value])- search_key
- Value to find.
- lookup_range
- Column or row that holds keys.
- result_range
- Column or row that holds return values.
- missing_value
- Optional replacement for #N/A.
XLOOKUP examples
SKU in G2, keys in A, prices in C.
=XLOOKUP(G2, A2:A200, C2:C200, "Missing")Returns C when A matches G2. The fourth argument replaces #N/A.
Key sits in C.
=XLOOKUP(G2, C2:C200, A2:A200)Lookup column can sit to the right of the result.
Need the full subset.
=FILTER(A2:D200, A2:A200=G2)FILTER is the Sheets habit when you want every hit, not one cell.
Common mistakes
Using XLOOKUP when you need every hit
Switch to FILTER or QUERY.
Mismatched range sizes
lookup_range and result_range must be the same height.
Assuming VLOOKUP left-lookup works
VLOOKUP cannot look left. XLOOKUP can.
XLOOKUP FAQ
XLOOKUP or FILTER in Sheets?
XLOOKUP returns one result. FILTER returns every matching row. Many Sheets files still prefer FILTER.
Does Sheets XLOOKUP need Excel 365?
No. It runs in the browser. Your teammate needs a current Sheets file, not a desktop build.
What about VLOOKUP?
VLOOKUP still works. XLOOKUP removes the column-index and left-lookup limits.