Every lookup guide on xlncad in one place: VLOOKUP and XLOOKUP basics, INDEX MATCH, exact and approximate match, multiple criteria, fixing #N/A, and returning several matches with dynamic arrays. Start at the top if you're new, or jump to the problem you're solving.
Which lookup should I use?
| You want to… | Excel 365 / 2021 | Excel 2019 and older |
|---|---|---|
| Find one value by an ID or name | XLOOKUP | VLOOKUP with FALSE |
| Return a column to the left | XLOOKUP | INDEX MATCH |
| Match a row and a column | XLOOKUP inside XLOOKUP | INDEX with two MATCHes |
| Look up a band, slab or grade | XLOOKUP with match mode -1 | VLOOKUP with TRUE |
| Match on two or more criteria | XLOOKUP or FILTER | INDEX MATCH with multiplied tests |
| Get every match, not just the first | FILTER | INDEX + AGGREGATE |
| Show a message when not found | XLOOKUP's 4th argument | IFNA |
Start here
- VLOOKUP function: the classic lookup, its four arguments and its limits.
- XLOOKUP function: the modern replacement, with left lookups and a built-in not-found message.
- XLOOKUP vs VLOOKUP: 8 reasons to switch: side-by-side examples, and when VLOOKUP is still fine.
Lookup guides
- INDEX MATCH in Excel: left lookups, two-way lookups and multiple criteria in any version.
- Exact vs approximate match: grade bands and slabs, and the missing-FALSE trap.
- Fix #N/A errors: extra spaces, numbers stored as text, IFNA vs IFERROR.
- Return multiple matches: FILTER, TEXTJOIN and other dynamic array lookups.
Lookup formulas for specific jobs
- VLOOKUP with multiple criteria
- Two-way lookup (XLOOKUP and INDEX MATCH)
- Lookup from another sheet
- VLOOKUP from two or more tables
- Compare two columns for matches
- Picture lookup
Lookup and reference functions
| Function | What it does |
|---|---|
| VLOOKUP / HLOOKUP | Look down the first column / across the first row |
| XLOOKUP | Look up in any direction, exact by default |
| INDEX | Return the value at a row and column position |
| MATCH / XMATCH | Return the position of a value |
| LOOKUP | Approximate lookup, handy for the last value in a column |
| FILTER | Return every row that meets a condition |
| CHOOSECOLS | Keep only the columns you need from a result |
| IFNA / IFERROR | Replace #N/A (or any error) with your own value |
| INDIRECT | Build a reference from text, for example a sheet name in a cell |