Excel · Lookups

INDEX + MATCH & XLOOKUP: fixing VLOOKUP's limits

The follow-up to "explain VLOOKUP" is almost always "what are its limitations, and what would you use instead?" This lesson is that answer.

Your win todayLook up a value that sits to the left of the key, three ways, and say which one you'd choose and why.

Split the job in two

VLOOKUP does "find" and "fetch" in one go, and that's why it's rigid. INDEX and MATCH each do one half:^[MS]

MATCH: value → position

=MATCH("Meera", B2:B6, 0) → 3
"Meera is the 3rd item." The last argument 0 means exact match. Like VLOOKUP, the default (1) is approximate.

INDEX: position → value

=INDEX(A2:A6, 3) → E103
"Give me the 3rd item of this range." Takes any range, on either side.

MATCH answers "which row?", and INDEX answers "what's in that row of this other column?". The two ranges only need the same height. They can sit in any order.

Why interviewers like INDEX+MATCH

Looks in any direction. The return column can be left of the key.
Survives inserted columns. You point at the column itself, not a hard-coded "3", so inserting a column doesn't break it.
Two-way lookup. Add a second MATCH for the column:
=INDEX(B2:D6, MATCH("E104", A2:A6, 0), MATCH("Dept", B1:D1, 0)) → Sales

XLOOKUP: the modern one-function version

=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )

=XLOOKUP("Meera", B2:B6, A2:A6, "Not found")   → E103

Exact match by default. The VLOOKUP trap from Lesson 1 is gone.^[MS]
Any direction: separate lookup and return ranges, the same idea as INDEX+MATCH.
Built-in if_not_found, so you don't need to wrap it in IFERROR.
search_mode -1 searches from the bottom up, giving the last match (e.g., a customer's latest order).
Not available in Excel 2016 or 2019.^[MS] Know INDEX+MATCH as the fallback.

The comparison table interviewers want

	VLOOKUP	INDEX + MATCH	XLOOKUP
Look left?	❌	✅	✅
Default match	Approximate	Approximate (MATCH's default is 1)	Exact
Safe if a column is inserted?	❌ hard-coded index	✅	✅
Not-found handling	Wrap in IFERROR	Wrap in IFERROR	Built-in argument
Last match?	❌	Awkward	✅ search_mode -1
Works in Excel 2016/2019?	✅	✅	❌

Say it in the interview"MATCH returns the position of a value, and INDEX returns the value at a position. Nesting them lets MATCH find the row and INDEX fetch it from any column. So it can look left, and it doesn't break when columns are inserted. On Microsoft 365 I'd use XLOOKUP: exact match by default, any direction, and a built-in not-found value. On older Excel I fall back to INDEX-MATCH."

Retrieval check

What does =MATCH("Ravi", B2:B6, 0) return?

Inside INDEX(…, MATCH(…)), what does MATCH supply?

What is XLOOKUP's default match mode?

The client runs Excel 2016 and needs a left lookup. What do you use?

Someone inserts a column in the middle of your table. What happens to INDEX+MATCH?

Type it

G2 holds a name. Return that person's EmpID with INDEX + MATCH.

Do the same with XLOOKUP, showing "Not found" if the name is missing.

Your turn

In your Lesson 1 sheet, try =INDEX(A2:A6,4) and =MATCH("Fatima",B2:B6,0) separately. Predict each first.
Combine them to get Fatima's EmpID (a left lookup).
Keep your Lesson 1 VLOOKUP for Dept (col 3). Insert a column between B and C. Watch VLOOKUP break and INDEX+MATCH survive.
Build the two-way lookup: =INDEX(B2:D6, MATCH("E104",A2:A6,0), MATCH("Dept",B1:D1,0)).
Add a second "Asha" row. Compare XLOOKUP with search_mode 1 against search_mode -1 (first vs last match).
Fill in the comparison table on paper from memory, then check it here.

Go deeper

Primary source: Microsoft Support: XLOOKUP function. Read the syntax table and the "match_mode / search_mode" rows. Then Look up values with VLOOKUP, INDEX, or MATCH.

💬 Ask your teacher. Try: "give me 5 more INDEX-MATCH practice questions" or "when would VLOOKUP still be fine?"