XLOOKUP
Look up a value in one range and return the matching value from another.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array)| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value to search for. |
lookup_array | Required | The range to search in. |
return_array | Required | The range to return a value from, matched by position. |
Examples
Every example below was executed against VisiGrid engine 0.19.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=XLOOKUP("y",B2:B4,A2:A4) | 20 | Column B holds x, y, x; column A holds 10, 20, 30. "y" is the second entry. |
Why it replaced VLOOKUP
VLOOKUP requires the lookup column to sit to the left of the returned column,
and it addresses the result by counting columns — so inserting a column silently
breaks every formula that counted past it. XLOOKUP takes two independent
ranges instead. They can be in any order, in any position, and inserting a
column between them changes nothing.
If you are choosing between them for new work, choose XLOOKUP. VLOOKUP is
supported for opening existing files, not because it is the better tool.
Searching in either direction
Because the lookup and return ranges are independent, XLOOKUP searches
leftward as easily as rightward. The lookup range can be to the right of the
range you want back — an arrangement VLOOKUP cannot express at all, and the
usual reason people end up nesting INDEX and MATCH.