Excel for Analysts · 14 min · 160 XP
Lookups: XLOOKUP, and why VLOOKUP bites
Pull a value from another table by its key, handle keys that aren't there, and avoid VLOOKUP's silent failures.
A lookup fetches a value from another table using a key: the price for a product code, or the region for a customer id. XLOOKUP (in Microsoft 365, Excel 2021 and later, and Google Sheets) takes what to find, where to find it, and what to return, and matches exactly by default.
=XLOOKUP(B2, $H$2:$H$7, $J$2:$J$7) the price for the code in B2 =XLOOKUP(B2, $H$2:$H$7, $J$2:$J$7, "Not found") …with a value for missing codes
When the key isn't in the table, a lookup returns #N/A. That's information, not noise: a product code that doesn't exist should be visible. Prefer XLOOKUP's own if not found argument to wrapping everything in IFERROR, because IFERROR also hides the typos and broken ranges you'd want to see.
Why VLOOKUP bites. =VLOOKUP(B2, H2:J7, 3, FALSE) does the same job, with three traps. First, its last argument defaults to approximate match, so leaving off FALSE on an unsorted code list returns a wrong row with no error at all. Second, the column number 3 is typed in, so inserting a column in the table silently shifts the result. Third, it can only return columns to the right of the key. If you're stuck with an old Excel, INDEX(J2:J7, MATCH(B2, H2:H7, 0)) avoids the last two.
=VLOOKUP("XXX-999", $H$2:$J$7, 3) → 90
There is no product XXX-999, and there's no error either.Loading your workspace…