Chapter 08 · intermediate · 6 min
Lookups: VLOOKUP, INDEX+MATCH, XLOOKUP
A lookup is Excel's version of a JOIN: given a key, pull matching data from another table. VLOOKUP is the function everyone learns first — and the one whose limits catch the most people, because it only searches its own leftmost column and only returns columns to the right of it. INDEX+MATCH and XLOOKUP both lift that restriction.
We'll work off one small staff table throughout: department, employee ID, name.
The grid
A staff table in A1:C5: department, employee ID and name, with headings in row 1. E2 holds the employee ID to look up; columns F to H are where the lookups go.
VLOOKUP: the shape, and the FALSE you must not skip
=VLOOKUP(E2,B2:C5,2,FALSE)VLOOKUP searches the first column of the range you give it (here, B, the employee ID) and returns a column counted rightward from there — 2 means the second column of the range, which is C, the name. The fourth argument, FALSE, demands an exact match; leave it off and VLOOKUP defaults to an approximate match against unsorted data, which is almost never what you want.
Notice the range starts at row 2, not row 1: the headings are labels, not data, and leaving them out keeps them from ever being matched.
E2 is "E103". Which name sits in the same row as E103 in the table?
Scroll to see all 8 columns →
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Department | Employee ID | Name | Find ID | Name | Dept (INDEX) | Dept (XLOOKUP) | |
| 2 | Ops | E101 | Aiman | E103 | =VLOOKUP(E2,B2:C5,2,FALSE) | |||
| 3 | Finance | E102 | Bella | |||||
| 4 | Ops | E103 | Chong | |||||
| 5 | IT | E104 | Dinesh |
VLOOKUP's four failure modes
It can't look left. The column you search must be the leftmost column of the range you give it — asking for the department (column A) while searching by employee ID (column B) is structurally impossible for VLOOKUP, not just awkward.
It breaks when a column is inserted. The 2 in the formula above means "2 columns over," a hardcoded position — insert a new column between B and C and every VLOOKUP pointing at "2" now reads the wrong column, silently.
Omitting the fourth argument defaults to approximate match. On data that isn't sorted ascending by the lookup column, an approximate match returns a plausible-looking wrong answer rather than an error.
It returns #N/A on anything that isn't an exact match — a typo, a trailing space, a number stored as text. No error is thrown until then; the formula just silently returns nothing useful.
INDEX+MATCH: lookup in any direction
MATCH finds a value's position in a range; INDEX returns whatever sits at a given position in another range. Combined, they can return a column to the left of the search column — something VLOOKUP cannot do at all:
=INDEX(A2:A5,MATCH(E2,B2:B5,0))MATCH(E2,B2:B5,0) finds E103's position within B2:B5 — 3. It sits on row 4 of the sheet, but MATCH counts from the top of the range you gave it, not from the top of the sheet, and INDEX counts the same way, so the two agree. INDEX(A2:A5,3) then returns the 3rd entry of A2:A5: the department. The 0 in MATCH means exact match, INDEX+MATCH's equivalent of VLOOKUP's FALSE.
E103 is Chong, the third person in the table. What department is on that row?
Scroll to see all 8 columns →
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Department | Employee ID | Name | Find ID | Name | Dept (INDEX) | Dept (XLOOKUP) | |
| 2 | Ops | E101 | Aiman | E103 | =INDEX(A2:A5,MATCH(E2,B2:B5,0)) | |||
| 3 | Finance | E102 | Bella | |||||
| 4 | Ops | E103 | Chong | |||||
| 5 | IT | E104 | Dinesh |
How Excel works it out · run it to see each value
- 1MATCH(E2,B2:B5,0)=?E103 is 3rd in the list (sheet row 4)
- 2INDEX(A2:A5,3)=?the 3rd department
- 3INDEX(A2:A5,MATCH(E2,B2:B5,0))=?same thing, in one formula
XLOOKUP: the modern replacement
XLOOKUP does what INDEX+MATCH does in one function, with exact match as the default (no FALSE/0 to remember) and no column-counting to get wrong:
=XLOOKUP(E2,B2:B5,A2:A5)XLOOKUP(lookup_value, lookup_array, return_array) — the array you search and the array you return are two separate arguments, so "look left" is never a special case, it's just which array you pass second. Prefer XLOOKUP for new work; recognise INDEX+MATCH, since a lot of existing spreadsheets still use it.
Same leftward lookup, via XLOOKUP
In H2, look up E2's department using XLOOKUP instead of INDEX+MATCH.
Scroll to see all 8 columns →
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Department | Employee ID | Name | Find ID | Name | Dept (INDEX) | Dept (XLOOKUP) | |
| 2 | Ops | E101 | Aiman | E103 | ||||
| 3 | Finance | E102 | Bella | |||||
| 4 | Ops | E103 | Chong | |||||
| 5 | IT | E104 | Dinesh |