How to Use INDEX/MATCH in Excel

Key Insights

• INDEX/MATCH provides bidirectional lookup capability that VLOOKUP cannot, allowing you to search right-to-left and making formulas more resilient to column changes
• Combining INDEX with two MATCH functions enables powerful two-way lookups across matrix-style data tables without restructuring your data
• Wrapping INDEX/MATCH in IFERROR and using absolute references prevents common errors and makes formulas more maintainable in production spreadsheets

Why INDEX/MATCH Beats VLOOKUP

VLOOKUP has been the default lookup function for Excel users for decades, but it comes with significant limitations that cause real problems in production spreadsheets. The most glaring issue: VLOOKUP only searches left-to-right. If your lookup column isn’t the leftmost column in your range, you’re out of luck. You’ll need to restructure your data or insert helper columns—both terrible solutions.

The second major problem is column position dependency. When you write VLOOKUP(A2, B:E, 3, FALSE), that “3” is hardcoded. If someone inserts a column into your data range, your formula breaks silently, returning wrong data without any error message. This is how spreadsheet mistakes make it into executive presentations.

INDEX/MATCH solves both problems. It searches in any direction, and it references actual ranges rather than column numbers. When your data structure changes, INDEX/MATCH formulas adapt automatically. For large datasets, INDEX/MATCH also performs faster because it only searches the lookup column rather than the entire table range.

Use INDEX/MATCH as your default lookup method. Reserve VLOOKUP only for quick ad-hoc analysis where you’re certain the data structure won’t change.

Understanding the INDEX Function

INDEX retrieves a value from a specific position within a range. Think of it as coordinates on a map—you specify the row and column, and INDEX returns what’s at that location.

The syntax is straightforward:

=INDEX(array, row_num, [column_num])

For a single column or row, you only need the row number. Here’s a simple example:

=INDEX(B2:B10, 5)

This returns the value in the 5th position of the range B2:B10, which would be cell B6. If you’re working with a multi-column range, add the column number:

=INDEX(B2:D10, 5, 2)

This returns the value at the 5th row and 2nd column of the range B2:D10, which would be cell C6.

The power of INDEX emerges when you replace those hardcoded position numbers with dynamic values from other functions—specifically, MATCH.

Understanding the MATCH Function

MATCH searches for a value within a range and returns its position. It’s the complement to INDEX: instead of “give me the value at position 5,” MATCH answers “what position is this value at?”

The syntax includes an often-misunderstood third parameter:

=MATCH(lookup_value, lookup_array, [match_type])

The match_type parameter controls how MATCH searches:

• 0: Exact match (use this 95% of the time)
• 1: Finds the largest value less than or equal to lookup_value (requires sorted data)
• -1: Finds the smallest value greater than or equal to lookup_value (requires sorted data)

Here’s a practical example. Suppose you have product names in A2:A20 and want to find where “Widget Pro” appears:

=MATCH("Widget Pro", A2:A20, 0)

If “Widget Pro” is in cell A8, this formula returns 7 (because A8 is the 7th cell in the range A2:A20). If the product isn’t found, you get a #N/A error.

The key insight: MATCH gives you a position number, which is exactly what INDEX needs.

Combining INDEX and MATCH for Basic Lookups

When you nest MATCH inside INDEX, you create a dynamic lookup that finds a value’s position and retrieves data from that same position in a different column. This is where INDEX/MATCH becomes genuinely useful.

The basic pattern looks like this:

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

Let’s work through a concrete example. You have employee IDs in column A and salaries in column D. You want to look up the salary for employee ID “E1043”:

=INDEX(D2:D100, MATCH("E1043", A2:A100, 0))

Here’s how Excel processes this formula:

1. MATCH searches for “E1043” in A2:A100
2. MATCH finds it in cell A25 and returns 24 (the 24th position in the range)
3. INDEX takes that position number and returns the value from the 24th cell in D2:D100
4. You get the salary from D25

Notice this works even though the salary column (D) is to the right of the lookup column (A). More importantly, if you need to look up data to the left of your lookup column, INDEX/MATCH handles it effortlessly:

=INDEX(B2:B100, MATCH("E1043", D2:D100, 0))

This searches for an employee ID in column D and returns the corresponding name from column B—impossible with VLOOKUP.

Advanced: Two-Way Lookups with INDEX/MATCH/MATCH

The real power move is using INDEX with two MATCH functions to perform lookups across both rows and columns simultaneously. This is perfect for matrix-style data where both dimensions contain meaningful categories.

The syntax extends the basic pattern:

=INDEX(data_array, MATCH(row_value, row_array, 0), MATCH(col_value, col_array, 0))

Imagine you have a sales table with products in rows and months in columns. Products are listed in A2:A10, months are in B1:M1, and sales data fills B2:M10. To find sales for “Widget Pro” in “June”:

=INDEX(B2:M10, MATCH("Widget Pro", A2:A10, 0), MATCH("June", B1:M1, 0))

Excel processes this in three steps:

1. The first MATCH finds “Widget Pro” in the product list and returns its row position
2. The second MATCH finds “June” in the month headers and returns its column position
3. INDEX uses both positions to retrieve the exact cell value from the intersection

This approach eliminates the need for complex nested IFs or restructuring your data into a flat table. Your data stays in its natural, readable matrix format.

Common Use Cases and Best Practices

In production spreadsheets, always wrap INDEX/MATCH in error handling. The IFERROR function catches #N/A errors when lookups fail and lets you display something meaningful:

=IFERROR(INDEX(D2:D100, MATCH(A2, B2:B100, 0)), "Not Found")

For Excel 2013 and later, IFNA is more precise—it only catches #N/A errors, letting other errors (like #REF!) surface for debugging:

=IFNA(INDEX(D2:D100, MATCH(A2, B2:B100, 0)), "Not Found")

Named ranges make formulas dramatically more readable. Instead of:

=INDEX(D2:D100, MATCH(A2, B2:B100, 0))

Define named ranges (Formulas → Name Manager) and write:

=INDEX(Salaries, MATCH(A2, EmployeeIDs, 0))

This is self-documenting code. Six months later, you’ll instantly understand what this formula does.

For large datasets (10,000+ rows), INDEX/MATCH outperforms VLOOKUP because it only searches the lookup column. However, if you’re doing thousands of lookups, consider using Excel tables with structured references or upgrading to XLOOKUP (available in Excel 365), which combines the best features of VLOOKUP and INDEX/MATCH with cleaner syntax.

Always use absolute references for your lookup ranges:

=INDEX($D$2:$D$100, MATCH(A2, $B$2:$B$100, 0))

This lets you copy the formula down rows while keeping the lookup ranges fixed. Only the lookup value (A2) changes as you copy.

Troubleshooting Common Errors

#N/A errors mean MATCH couldn’t find your lookup value. Common causes:

• Typos or extra spaces in data (use TRIM to clean data)
• Different data types (text “100” vs. number 100)
• Wrong match_type parameter (using 1 or -1 instead of 0)

#REF! errors indicate invalid cell references, usually because:

• Your INDEX range and MATCH range have different sizes
• Rows or columns were deleted, breaking your references
• You forgot to use absolute references when copying formulas

Here’s a before/after example showing proper absolute references:

// Wrong - breaks when copied down
=INDEX(D2:D100, MATCH(A2, B2:B100, 0))

// Correct - works when copied anywhere
=INDEX($D$2:$D$100, MATCH(A2, $B$2:$B$100, 0))

When debugging, break the formula into parts. Calculate the MATCH separately to verify it returns the expected position number:

=MATCH(A2, $B$2:$B$100, 0)

If this returns the right number but INDEX still fails, your INDEX range is the problem.

For data type mismatches, use VALUE to convert text to numbers or TEXT to convert numbers to text:

=INDEX($D$2:$D$100, MATCH(VALUE(A2), $B$2:$B$100, 0))

INDEX/MATCH represents a fundamental upgrade in how you work with Excel data. It’s more flexible, more maintainable, and more performant than VLOOKUP. The syntax takes a few extra minutes to learn, but you’ll recoup that time investment the first time you need to insert a column or perform a right-to-left lookup. Make INDEX/MATCH your default, and your spreadsheets will be more robust and professional.