INDEX MATCH
The powerful alternative to VLOOKUP
INDEX MATCH - Two Functions That Work Together
INDEX and MATCH are like a team. Together, they can find anything in your data!
What Problem Does This Solve?
Imagine you have a list of 100 products. Someone asks: "What is the price of Banana?"
You need to:
- Find where "Banana" is in the list
- Get the price from that same row
INDEX MATCH does both steps automatically!
Part 1: Understanding INDEX
INDEX picks a value from a list by position.
Formula: =INDEX(list, position_number)
Example
| A (Fruit) |
|---|
| Apple |
| Banana |
| Orange |
Get the 2nd fruit:
=INDEX(A1:A3, 2) → "Banana"
Think of it as: "Give me item number 2 from this list"
Practice: Get Value by Position
EasyUse INDEX to get the 3rd value from the list in A1:A4.
Part 2: Understanding MATCH
MATCH finds the position of a value in a list.
Formula: =MATCH(what_to_find, where_to_look, 0)
The 0 means "exact match" - always use it!
Example
| A (Fruit) |
|---|
| Apple |
| Banana |
| Orange |
Find position of "Banana":
=MATCH("Banana", A1:A3, 0) → 2
This means "Banana is in position 2"
Practice: Find Position
EasyUse MATCH to find the position of "Blue" in A1:A4.
Part 3: Combine Them!
Now use both together:
- MATCH finds the position
- INDEX gets the value at that position
Example: Find Price
| A (Product) | B (Price) |
|---|---|
| Apple | 50 |
| Banana | 30 |
| Orange | 40 |
Find price of "Banana":
=INDEX(B1:B3, MATCH("Banana", A1:A3, 0))
How it works:
MATCH("Banana", A1:A3, 0)→ 2 (Banana is in row 2)INDEX(B1:B3, 2)→ 30 (Get price from row 2)
Result: 30
Practice: INDEX MATCH Combo
EasyFind the price of "Orange" using INDEX MATCH. Products in A1:A3, Prices in B1:B3.
Why This is Better Than VLOOKUP
Can Search Backwards!
| A (Price) | B (Product) |
|---|---|
| 50 | Apple |
| 30 | Banana |
| 40 | Orange |
Find price of "Banana" (price is LEFT of product):
=INDEX(A1:A3, MATCH("Banana", B1:B3, 0)) → 30
VLOOKUP cannot do this! INDEX MATCH can.
Step-by-Step Guide
Formula: =INDEX(answer_column, MATCH(find_this, search_column, 0))
- answer_column: Where is the answer? (Price column)
- find_this: What are you looking for? ("Banana")
- search_column: Where to search? (Product column)
- 0: Always use 0 for exact match
Quick Reference
| Function | What It Does | Example |
|---|---|---|
| INDEX | Get value by position | =INDEX(A1:A10, 5) |
| MATCH | Find position of value | =MATCH("Apple", A1:A10, 0) |
| INDEX MATCH | Find and get value | =INDEX(B:B, MATCH("Apple", A:A, 0)) |
Common Mistakes
| Mistake | Why It's Wrong | How to Fix |
|---|---|---|
| Forget the 0 in MATCH | Gets wrong results | Always add 0: MATCH(value, range, 0) |
| Swap INDEX ranges | Returns wrong data | First range = answer column |
| Different sized ranges | Causes errors | Make sure both ranges have same rows |
Summary
INDEX: Gets value at position
=INDEX(A1:A10, 3)→ Get 3rd item
MATCH: Finds position of value
=MATCH("Apple", A1:A10, 0)→ Where is Apple?
Together:
=INDEX(B:B, MATCH("Apple", A:A, 0))- Find "Apple" in column A, return value from column B
This combo is powerful and flexible!