One of the most widely used lookup functions in Excel is VLOOKUP. However, VLOOKUP requires the lookup column to be on the left and can break when columns are inserted. A powerful alternative that solves these problems is the combination of INDEX and MATCH.
β INDEX Function Basics
Syntax:
=INDEX(array, row_num, [column_num])
Example: Return the value in row 3, column 2 of B2:D10
=INDEX(B2:D10, 3, 2)
β MATCH Function Basics
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Example: Find the position of βA102β in A2:A10
=MATCH("A102", A2:A10, 0)
0 indicates an exact match.
β Combining INDEX + MATCH
Syntax:
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))
Example: Find an employee name by employee ID
=INDEX(B2:B20, MATCH("E105", A2:A20, 0))
MATCH finds the position of βE105β in column A, and INDEX returns the value from column B at that position.
β Practical Example 1: Look Up a Value to the Left
Find a product code by product name (not possible with VLOOKUP)
=INDEX(A2:A20, MATCH("Printer", B2:B20, 0))
β Practical Example 2: Conditional Salary Lookup
Retrieve a salary based on department and employee name
=INDEX(D2:D50, MATCH(1, (B2:B50="Sales")*(C2:C50="Kim Cheolsu"), 0))
This uses an array formula to perform a multiple-criteria lookup.
β Practical Example 3: Monthly Sales Analysis
Retrieve sales for the selected month in cell G1
=INDEX(B2:M2, MATCH(G1, B1:M1, 0))
π Summary
- INDEX: Returns a value from a specific position
- MATCH: Finds the position of a value
- Using them together: Overcomes VLOOKUP limitations
- Practical uses: left-side lookups, multiple-criteria lookups, and reliable handling of large datasets
π Frequently Asked Questions (FAQ)
Q1. Why is INDEX + MATCH better than VLOOKUP?
A. It can look up values to the left, is safer when columns are inserted, and offers better speed.
Q2. What do 1 and -1 mean in MATCH?
A. 1 means an approximate match (ascending order), -1 means an approximate match (descending order), and 0 means an exact match.
Q3. How do I perform a multiple-criteria lookup?
A. Use an array formula: =INDEX(result_range, MATCH(1, (criteria1)*(criteria2), 0))
Q4. What should I do if INDEX MATCH does not work?
A. Check that the ranges are the same length and that MATCH is using an exact match.
Now use INDEX & MATCH to overcome VLOOKUP limitations. Your reports will be much more accurate and flexible π