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, EmployeeID, Name.
The grid
A staff table in A1:C4 — column A department, column B employee ID, column C name. E1 holds an employee ID to look up.
VLOOKUP: the shape, and the FALSE you must not skip
=VLOOKUP(E1,B1:C4,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 "one column to the right of B," 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.
E1 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 | Ops | E101 | Aiman | E103 | =VLOOKUP(E1,B1:C4,2,FALSE) | |||
| 2 | Finance | E102 | Bella | |||||
| 3 | Ops | E103 | Chong | |||||
| 4 | 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 Department (column A) while searching by EmployeeID (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(A1:A4,MATCH(E1,B1:B4,0))MATCH(E1,B1:B4,0) finds E103's position within B1:B4 — row 3. INDEX(A1:A4,3) then returns whatever is in the 3rd row of A1:A4: the department. The 0 in MATCH means exact match, INDEX+MATCH's equivalent of VLOOKUP's FALSE.
E103 is Chong, in row 3. What department is in row 3, column A?
Scroll to see all 8 columns →
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Ops | E101 | Aiman | E103 | =INDEX(A1:A4,MATCH(E1,B1:B4,0)) | |||
| 2 | Finance | E102 | Bella | |||||
| 3 | Ops | E103 | Chong | |||||
| 4 | IT | E104 | Dinesh |
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(E1,B1:B4,A1:A4)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 H1, look up E1's department using XLOOKUP instead of INDEX+MATCH.
Scroll to see all 8 columns →
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Ops | E101 | Aiman | E103 | ||||
| 2 | Finance | E102 | Bella | |||||
| 3 | Ops | E103 | Chong | |||||
| 4 | IT | E104 | Dinesh |