Lookup Formula Builder: XLOOKUP, VLOOKUP, INDEX/MATCH
Enter your ranges and get XLOOKUP, VLOOKUP and INDEX/MATCH formulas ready to paste.
Use XLOOKUP: returns column C
=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")=IFNA(VLOOKUP(F2,$A$2:$D$100,3,FALSE),"Not found")=IFNA(INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),"Not found")Which lookup should I use?
| Function | Works in | Good for |
|---|---|---|
| XLOOKUP | Excel for Microsoft 365, Excel 2021 and later, Excel for the web | Most lookups. Exact match by default, can look left, has a built-in “if not found”. |
| VLOOKUP | Every version | Simple tables where the answer is to the right of the lookup column. |
| INDEX and MATCH | Every version | Looking left, or sharing a file with people on older versions. |
Why the ranges have dollar signs
The tool writes the table as an absolute reference, such as $A$2:$D$100, so you can copy the formula down a column without the table shifting. The value to find, such as F2, is left relative so it moves with each row.
Exact or approximate match
Use exact match for IDs, names and codes. Approximate match finds the largest value that is less than or equal to the one you look up, which suits tax bands, grade boundaries and commission tiers. For VLOOKUP and MATCH, approximate match only works if the lookup column is sorted smallest to largest.
Fixing #N/A
#N/A means the value was not found. The usual causes are extra spaces (try TRIM), numbers stored as text in one table and as numbers in the other, or a lookup value missing from the table. The “If not found” box wraps the formula so it shows your message instead of the error; it does not fix the cause.
Sources: Function behaviour: Microsoft Support function reference. Results checked against worked examples, and generated formulas evaluated in the open-source HyperFormula engine. Method & sources