Mastering INDEX & MATCH (From Beginner to Pro)
✨ Why Learn INDEX , MATCH?
If you’ve ever hit a wall with VLOOKUP’s limitations or struggled with complex data lookups, it’s time to meet your new power couple: INDEX and MATCH. Together, they create flexible, efficient lookup solutions—even with multiple conditions.
What Is INDEX MATCH
The INDEX MATCH function combination is an alternative to the VLOOKUP function. The INDEX MATCH With Multiple Criteria helps users to retrieve the required data in a dataset where we may not find a unique column to apply the lookup function. Therefore, in such scenarios, we find a pattern to identify the unique lookup_value column, and the multiple criteria columns to create a unique column to apply the lookup logic to retrieve values.
Key Takeaways
- INDEX and MATCH functions can match multiple criteria with the helper column to create a unique column, and can also be used as nested functions to match multiple criteria.
- They retrieve the values from right to left, and vice versa, and are often used as an alternative to the VLOOKUP function.
- Two MATCH functions can be used for a two-way lookup of values when the column header changes.
- The combination of the VLOOKUP and CHOOSE functions can be used as an alternative to INDEX and MATCH with multiple criteria.
🔍 What Is INDEX?
INDEX(array, row_num) returns the value of a cell from a specific row in a range.
Example:
=INDEX(B2:B5, 2)
Returns the second value in the range B2:B5
🔎 What Is MATCH?
MATCH(lookup_value, lookup_array, 0) returns the position of the lookup value in a range.
Example:
=MATCH("Blue", B2:B5, 0)
Returns the row number where “Blue” appears in B2:B5.
🧩 Why Use INDEX + MATCH Together?
Because this combo is:
- More flexible than VLOOKUP
- Works in any direction (left or right)
- Supports multiple conditions—which VLOOKUP doesn’t
🔄 Basic INDEX + MATCH Combo
Goal: Find the price of “Burger”
=CONCATENATE("Burger Price: ", INDEX(C5:F9,MATCH("Burger",C5:C9,0),4))
✅ Using INDEX + MATCH with Multiple Conditions
Here’s where it gets exciting.
🧾 Example Dataset:
Product Name Type Size Price
Chicken Fry Original Regular Tk 65
Chicken Fry Original Large Tk 120
Chicken Fry Crispy Regular Tk 65
Burger Chicken Medium Tk 160
Juice Strawberry Normal TK 120
🎯 Goal:
Find the price of a product where:
- Product Name = chicken fry
- Type = original
- Size = large
💡 Formula:
=IFERROR( INDEX(C5:F9,MATCH(1,(I10=C5:C9)*(J10=D5:D9)*(K10=E5:E9),0),4),"Please check ")
If you're using Excel 2016 or earlier, press Ctrl + Shift + Enter to make it an array formula.
⚙️ Why Use 1 and *?
Each condition returns a TRUE/FALSE array:
- TRUE = 1
- FALSE = 0
Multiplying the arrays means only the row where all conditions are TRUE will equal 1. That’s what MATCH(1, …) is looking for.
🔥 Pro Tips
- Wrap formula in IFERROR to handle missing matches:
- =IFERROR(..., "Please check")
🧠 Final Thoughts
The INDEX + MATCH combo with multiple criteria unlocks powerful capabilities that every data-driven professional should master. Whether you’re building dashboards, reports, or automated tools, this pattern will save you time—and impress your team.
Back to Blogs