Introduction
Looking things up is one of the most common jobs in Excel: the price of a product code, the name for an employee ID, the tax rate for an income. Typing these by hand is slow and error-prone. Lookup functions find a key in one table and return a related value, and they update automatically when the table changes.
How it works
VLOOKUP searches down the first column of a table and returns a value from a column you choose by number:
=VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE)
This finds A2 in the first column of the product table and returns the value from its third column. The last argument decides the match type: FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match. If you leave it out, VLOOKUP assumes TRUE, which on an unsorted table quietly returns wrong answers: always write FALSE for exact lookups. VLOOKUP has two other limits: it can only return values to the right of the key column, and the column number breaks if someone inserts a column into the table. When the key is not found it returns #N/A.
XLOOKUP, in Excel 2021 and Microsoft 365, fixes these problems:
=XLOOKUP(A2,Products!A2:A100,Products!C2:C100,"Not found")
You give the column to search and the column to return separately, so the return column can be anywhere, even to the left. The match is exact by default, and the fourth argument is returned when nothing matches. Further optional arguments choose approximate matching and searching from the bottom up.
INDEX and MATCH work in every version. MATCH(A2,Products!A2:A100,0) returns the position of A2 in the list (0 means exact), and INDEX(Products!C2:C100,position) returns the value at that position:
=INDEX(Products!C2:C100,MATCH(A2,Products!A2:A100,0))
Like XLOOKUP, it can return from any column and survives inserted columns.
Approximate matches find bands. With a table of thresholds sorted ascending (0, 50, 65, 80) and grades (F, C, B, A), =VLOOKUP(B2,$E$2:$F$5,2,TRUE) returns the grade for the largest threshold not above the mark. With XLOOKUP, set the match mode to -1 (exact match or the next smaller item), which also works on an unsorted table.
Example
An order sheet looks up prices from a product table on the same sheet:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Price | Code | Name | Price | |
| 2 | B-2 | P-1 | Pen | 20 | ||
| 3 | P-9 | B-2 | Bag | 900 | ||
| 4 | N-3 | Notebook | 60 |
Line by line
- In B2 type
=XLOOKUP(A2,$D$2:$D$4,$F$2:$F$4,"Not found")and fill it down to B3. - In older versions, use
=IFNA(VLOOKUP(A2,$D$2:$F$4,3,FALSE),"Not found")instead. - In C2 type
=INDEX($E$2:$E$4,MATCH(A2,$D$2:$D$4,0))to return the product name with INDEX and MATCH.
Expected result
B2 shows 900, the price of the bag, and B3 shows Not found, because P-9 is not in the table. C2 shows Bag. C3 shows #N/A, because the INDEX and MATCH formula has no not-found value; wrap it in IFNA to show something friendlier.
In the real world
Lookups join information from different lists: prices onto orders, names onto employee IDs, budgets onto actual spending. Many organisations still run on VLOOKUP, so you need to read it, but XLOOKUP or INDEX with MATCH is the better choice for new work where the version allows.
Common mistakes
- Leaving out FALSE in VLOOKUP, which uses an approximate match and returns wrong values.
- Relative table references that slide down as the formula is copied; lock them with $.
- Keys that look equal but are not: a number stored as text, or trailing spaces.
- Hard-coded column numbers in VLOOKUP that break when columns are inserted.
Best practices
- Use XLOOKUP where every user has Excel 2021 or Microsoft 365; otherwise INDEX with MATCH.
- Put lookup tables on their own sheet or in an Excel Table, which keeps ranges correct as rows are added.
- Handle not-found keys on purpose with XLOOKUP's fourth argument or IFNA.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Lock a reference while editing | F4 | Command+T |
| Insert a function | Shift+F3 | Shift+F3 |
| Find a key in the sheet | Ctrl+F | Command+F |
| Next worksheet, to the lookup table | Ctrl+Page Down | Option+Right Arrow |
| Fill a formula down | Ctrl+D | Command+D |
Summary
VLOOKUP searches a table's first column and returns a column by number, and needs FALSE for exact matches. XLOOKUP, in Excel 2021 and Microsoft 365, separates the search and return ranges, is exact by default and can return a not-found value. INDEX with MATCH does the same in any version, and approximate matches turn sorted thresholds into bands.
Quick check
Key takeaways
- VLOOKUP searches the first column and needs FALSE (or 0) as its last argument for an exact match.
- XLOOKUP is exact by default, can look left and can return a value when nothing is found.
- INDEX with MATCH works in every version and does not break when columns are inserted.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.