Course / Lookups

INDEX and MATCH

Find a position with MATCH, fetch a value with INDEX, then combine them to look up in any direction. Intermediate · 5 min · INDEX, MATCH, Left lookup

VLOOKUP has two weak spots: it can only search the first column of a table, and a hard-coded column number breaks when someone inserts a column. INDEX and MATCH fix both.

• =INDEX(range, n) returns the *n*th item in a range
• =MATCH(value, range, 0) returns the position of a value in a range. The 0 asks for an exact match

Put them together and MATCH finds the row while INDEX fetches the answer from any column you like, even one to the left of the column you searched.

Steps

1. Start with INDEX on its own. In G2, return the 3rd name in the directory: =INDEX(A2:A9, 3)

2. Now the opposite question. In G5, find where the name in G4 appears in the list: =MATCH(G4, A2:A9, 0)

3. Combine them. In G6, return Emeka's department in a single formula: MATCH finds the position, and INDEX reads that position from the Department column. =INDEX(B2:B9, MATCH(G4, A2:A9, 0))

4. A voicemail quotes the staff ID in G8. In G9, find that person's name. The IDs are in column D, to the right of the names, so VLOOKUP can't do this one.

Remember

• INDEX(range, n) returns the nth item in a range.
• MATCH(value, range, 0) returns the position of an exact match.
• INDEX(return_range, MATCH(value, search_range, 0)) looks up in any direction.
• No column numbers to count, so inserted columns won't break it.

Previous: Approximate match for tiers and bands · Next: XLOOKUP