XLOOKUP in Google Sheets
XLOOKUP finds a value in one range and returns the matching item from another range. It replaces VLOOKUP and works left, right, up or down.
=XLOOKUP(E2, A2:A100, B2:B100)
Looks for E2 in A2:A100 and returns the value from the same row of B2:B100.
How it works
XLOOKUP takes a search key, the lookup range to search, and the result range to return from. Unlike VLOOKUP it doesn't care about column order — the result range can be to the left of the lookup range. An optional 4th argument sets what to show when nothing is found.
Variations
Show text instead of #N/A when not found
=XLOOKUP(E2, A2:A100, B2:B100, "Not found")
The 4th argument is the missing-value fallback.
Return a column to the LEFT of the key
=XLOOKUP(E2, B2:B100, A2:A100)
VLOOKUP can't do this; XLOOKUP can.
Find the last match instead of the first
=XLOOKUP(E2, A2:A100, B2:B100, , 0, -1)
The 6th argument -1 searches bottom-to-top.
Examples
| Scenario | Formula |
|---|---|
| Price by product name | =XLOOKUP("Widget", A2:A100, C2:C100) |
| Approximate (next-smaller) match on a sorted table | =XLOOKUP(E2, A2:A100, B2:B100, , -1) |
FAQ
Is XLOOKUP available in Google Sheets?
Yes, XLOOKUP is supported in Google Sheets. If it errors as unknown, your file may be very old; INDEX/MATCH is the equivalent fallback.
XLOOKUP vs VLOOKUP — which should I use?
Prefer XLOOKUP: it can look left, has a built-in not-found value, and doesn't break when you insert columns.