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.35.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. |
=XLOOKUP("102",A2:A2,B2:B2) | #N/A | XLOOKUP is strict — the Excel-correct answer, and the one the family is moving to. |
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.
For the full comparison — column counting, the approximate-match default, and
when VLOOKUP is still the right call — see
XLOOKUP vs VLOOKUP.
Handling a miss without a wrapper
The fourth argument is what to return when nothing matches. Change the ID to
one that isn’t there — 999, say — and watch the two rows diverge:
| ID | Rep |
| 101 | Ana |
| 102 | Bo |
| 103 | Cy |
| Look up ID: | 102 |
| With fallback: | Bo |
| Without: | Bo |
Edit the highlighted cell and the formula recalculates — computed by VisiGrid engine 0.35.0, in your browser. Nothing is saved.
VLOOKUP has no equivalent — it returns #N/A and you wrap it in IFERROR,
which also hides #REF! and every other error. See
IFERROR for why that matters.