FormulaLookups
Look up a value, with a fallback
The lookup that replaced VLOOKUP: exact match by default, any direction, and a built-in answer for when the value isn't found.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
=XLOOKUP(A2, Products!A:A, Products!C:C, "not listed")
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →The fourth argument is the whole upgrade: VLOOKUP returns #N/A on a miss and forces an IFERROR wrap around everything; XLOOKUP takes the fallback as part of the lookup itself.
- →Search and return are separate ranges, so the return column can sit LEFT of the search column — the restriction that made VLOOKUP layouts contort for years is simply gone.
- →Exact match is the default. VLOOKUP's silent default was approximate match, which is the classic source of confidently wrong lookups on unsorted data.
The fine print
- →Needs Excel 2021/365 or Google Sheets; older Excel wants INDEX+MATCH instead (that item is in this collection too).
- →Whole-column references are convenient but slow on very large sheets — bound the ranges (A2:A5000) when a workbook starts to crawl.
- →The two ranges must be the same length or the formula returns #VALUE!.