You receive a customer credit review file with only Customer IDs. The finance team needs customer name, city, segment, credit limit and outstanding amount from the customer master. INDEX MATCH can fetch these details without depending on column index numbers.

What does INDEX MATCH do?

INDEX MATCH is a lookup technique that combines two functions. MATCH finds the position of the lookup value, and INDEX returns the value from that position in the return column.

Simple rule: MATCH finds the row number. INDEX returns the value from that row.

INDEX MATCH formula structure

Basic syntax

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

Example: =INDEX('Customer Master'!B:B,MATCH(B4,'Customer Master'!A:A,0))

This finds the Customer ID in B4 inside the Customer ID column and returns the matching Customer Name from column B.

Business examples

NeedFormulaResult
Find customer name=INDEX(Master!B:B,MATCH(B4,Master!A:A,0))Aarav Traders
Find credit limit=INDEX(Master!F:F,MATCH(B4,Master!A:A,0))₹250,000
Wrong customer ID=IFERROR(INDEX(...),"Not Found")Not Found
INDEXReturns value
MATCHFinds position
0Exact match

When to use and when not to use INDEX MATCH

Use INDEX MATCH whenYou need flexible lookup logic or want to avoid VLOOKUP column index issues.
Avoid INDEX MATCH whenXLOOKUP is available and your team prefers simpler formulas.
Strong advantageIt can return values from the left or right of the lookup column.
Best for legacy filesMany advanced Excel workbooks still use INDEX MATCH.
💡
PM tip: Learn INDEX MATCH to understand older advanced reports, but use XLOOKUP for new files if your organization supports it.
⚠️
Common mistake: Forgetting 0 in MATCH. For IDs and codes, use 0 for exact match.

Interview question

Question: Why was INDEX MATCH preferred over VLOOKUP before XLOOKUP?

Answer: INDEX MATCH is more flexible because it can return values from left or right columns, does not depend on column index numbers, and is less likely to break when columns are inserted or moved.

🧪 Practice Lab

Fetch customer details using INDEX MATCH

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, formula cheat sheet, quiz and answer key.

📦 Download Lesson 20 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Use MATCH to find Customer ID position.
  2. Use INDEX MATCH to fetch Customer Name.
  3. Fetch City, Segment and Account Manager.
  4. Fetch Credit Limit and Outstanding amount.
  5. Use IFERROR to show “Not Found” for missing IDs.

Quick quiz

  1. What does INDEX do?
  2. What does MATCH do?
  3. What does 0 mean in MATCH?
  4. Can INDEX MATCH look left?

Answers: Returns value from a position; finds position; exact match; yes.

FAQ

It is more flexible than VLOOKUP, but XLOOKUP is usually easier for modern Excel users.
Yes, after learning VLOOKUP and XLOOKUP. It helps you understand older advanced workbooks and interview questions.