How to Use INDEX and MATCH in Excel

Microsoft Excel offers many powerful tools for finding and retrieving data, but few combinations are as flexible and reliable as INDEX and MATCH. Together, these two functions provide a modern alternative to older lookup functions like VLOOKUP and HLOOKUP, allowing users to build dynamic, efficient, and future-proof spreadsheets.

In this article, you will learn what INDEX and MATCH are, how they work individually, and how to combine them effectively. We will explore practical examples, common use cases, best practices, and mistakes to avoid. By the end, you will understand why INDEX and MATCH are considered essential skills for anyone working seriously with Excel.

Why Use INDEX and MATCH?

Before diving into the mechanics, it’s important to understand why INDEX and MATCH are so widely recommended.

Traditional lookup functions such as VLOOKUP and HLOOKUP have several limitations:

• They can only search from left to right or top to bottom
• They break when columns or rows are inserted
• They require fixed column numbers
• They can be slower on large datasets

INDEX and MATCH overcome all these issues. When combined, they allow you to:

• Look up values in any direction
• Create dynamic and flexible formulas
• Build spreadsheets that are easier to maintain
• Handle large datasets more efficiently

Understanding the INDEX Function

What Is INDEX?

The INDEX function returns the value of a cell in a specified position within a range.

INDEX Syntax

INDEX(array, row_num, [column_num])

Arguments Explained

• array – The range of cells containing the data
• row_num – The row number within the array
• column_num – (Optional) The column number within the array

Simple INDEX Example

Assume you have the following data in cells A2:C6:

To return the price of the third product, use:

=INDEX(C2:C6,3)

Excel returns 0.50, which corresponds to Carrot.

INDEX with Rows and Columns

To retrieve a value from a table with both rows and columns:

=INDEX(A2:C6,4,1)

This returns “Onion”, located in row 4, column 1 of the range.

Understanding the MATCH Function

What Is MATCH?

The MATCH function searches for a value in a range and returns its relative position.

MATCH Syntax

MATCH(lookup_value, lookup_array, [match_type])

Arguments Explained

• lookup_value – The value you want to find
• lookup_array – The range to search
• match_type – Specifies how Excel matches values

Match Types

In most lookup scenarios, 0 (exact match) is recommended.

Simple MATCH Example

To find the position of “Carrot” in column A:

=MATCH("Carrot",A2:A6,0)

Excel returns 3, because Carrot is the third item in the list.

Combining INDEX and MATCH

Why Combine Them?

INDEX returns a value based on a position. MATCH finds the position of a value.

Together, they create a powerful and flexible lookup formula.

Basic INDEX + MATCH Formula

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example: Lookup a Price by Product Name

Using the same product table:

To find the price of Pear:

=INDEX(C2:C6, MATCH("Pear", A2:A6, 0))

Excel works in two steps:

1. MATCH finds Pear’s position (row 5)
2. INDEX returns the value from C2:C6 at row 5

Result: 1.10

INDEX and MATCH with Two Criteria

One of the major strengths of INDEX and MATCH is the ability to perform multi-criteria lookups, something VLOOKUP cannot do easily.

Example: Lookup with Two Conditions

Suppose you have:

To find Apple sales in the West region:

=INDEX(C2:C5, MATCH(1, (A2:A5="Apple")*(B2:B5="West"), 0))

In older Excel versions, press Ctrl + Shift + Enter. In modern Excel, this works automatically.

Using INDEX and MATCH Horizontally

Unlike VLOOKUP, INDEX and MATCH work both vertically and horizontally.

Horizontal Lookup Example

If your data is arranged across columns:

To retrieve February sales:

=INDEX(B2:D2, MATCH("Feb", B1:D1, 0))

This returns 135.

INDEX and MATCH with Dynamic Ranges

INDEX and MATCH pair well with dynamic named ranges, structured tables, and expanding datasets.

Example with Excel Tables

If your data is in a table named SalesData:

=INDEX(SalesData[Revenue], MATCH("Apple", SalesData[Product], 0))

This formula automatically adjusts when new rows are added.

Handling Errors with INDEX and MATCH

If a value is not found, Excel returns #N/A. To handle this gracefully, wrap the formula in IFERROR.

Example

=IFERROR(INDEX(C2:C6, MATCH("Mango", A2:A6, 0)), "Not Found")

Instead of an error, Excel displays “Not Found”.

INDEX and MATCH vs VLOOKUP

For modern spreadsheets, INDEX and MATCH are generally the better choice.

Common Mistakes to Avoid

1. Using approximate match unintentionally Always specify 0 for exact matches unless you truly need approximation.

2. Mismatched ranges The lookup range and return range must align correctly.

3. Not locking references Use $ to lock ranges when copying formulas.

4. Forgetting error handling Use IFERROR to improve usability.

Best Practices

• Always use exact match (0) unless sorting is guaranteed
• Keep lookup ranges clean and consistent
• Use Excel Tables for dynamic formulas
• Combine with IFERROR for user-friendly results
• Document complex formulas with comments or helper cells

When to Use INDEX and MATCH

INDEX and MATCH are ideal when:

• Your data structure may change
• You need left-side or horizontal lookups
• You work with large datasets
• You need advanced or multi-criteria lookups
• You want long-term spreadsheet reliability

Final Thoughts

INDEX and MATCH are among the most important Excel functions to master. While they may appear more complex than VLOOKUP at first, the flexibility and robustness they offer make them well worth the effort. Once understood, they open the door to cleaner formulas, better performance, and far more powerful data analysis.

If you regularly work with spreadsheets, learning how to use INDEX and MATCH is not just a nice-to-have skill—it is an essential one.