🔎 Advanced INDEX + MATCH Techniques: Overcome VLOOKUP Limitations

Have you ever been frustrated by VLOOKUP limitations when looking up data in Excel? That is where the INDEX + MATCH combination comes in. These two functions provide remarkable flexibility when used together.

✅ INDEX Function Basics

=INDEX(range, row_num, [column_num])

Example: The value in the third row and second column of the B2:D10 range

=INDEX(B2:D10, 3, 2)

✅ MATCH Function Basics

=MATCH(lookup_value, lookup_array, [match_type])

Example: The position of “Seoul” in A2:A10

=MATCH("Seoul", A2:A10, 0)

✅ Combining INDEX + MATCH

Use MATCH to find the position, then use INDEX to return the corresponding value.

=INDEX(C2:C100, MATCH("P103", A2:A100, 0))

→ Return the price for product code P103

✅ Practical Example 1: Left Lookup

Use INDEX + MATCH to perform a left lookup that VLOOKUP cannot handle.

=INDEX(A2:A100, MATCH("Seoul", B2:B100, 0))

✅ Practical Example 2: Multiple-Criteria Lookup

Find a customer whose region is “Seoul” and whose grade is “VIP.”

=INDEX(C2:C100, MATCH(1, (A2:A100="Seoul")*(B2:B100="VIP"), 0))

Requires an array formula (Ctrl+Shift+Enter).

✅ Practical Example 3: Find the Last Value

Return the last transaction date from a list of sales dates.

=INDEX(B2:B100, MATCH(9.99999999999999E+307, B2:B100))

✅ Practical Example 4: Find a Value Within a Range

Display a grade based on a score.

=INDEX({"F","D","B","A"}, MATCH(score, {0,60,80,90}, 1))

📌 Summary

• 📍 INDEX: Returns a value based on its position
• 📍 MATCH: Finds the position of a value
• 📍 Together, they overcome VLOOKUP limitations
• 📍 Supports multiple criteria, left lookups, and last-value lookups

🙋 Frequently Asked Questions (FAQ)

The INDEX + MATCH combination is a core tool for report automation and data analysis. Try it today 🚀