How to Use INDEX and MATCH in Excel: A Complete Guide

INDEX and MATCH are two of the most useful Excel functions for looking up and retrieving data. When combined, they provide a flexible alternative to traditional lookup functions such as VLOOKUP and HLOOKUP.

The INDEX MATCH combination is particularly useful when you need to look up values to the left, work with large tables, perform two-way lookups, or build more flexible Excel formulas.

This guide explains how INDEX and MATCH work, how to combine them, and when you should use them in real-world Excel spreadsheets.

What Is the INDEX Function in Excel?

The INDEX function returns a value from a specific position within a range or array.

The basic syntax is:

=INDEX(array, row_num, [column_num])

Where:

array is the range containing the data.
row_num specifies the row position.
column_num specifies the column position.

For example, consider the following table:

Product ID	Product	Price
SP01	Laptop	15,000,000
SP02	Smartphone	10,000,000
SP03	Tablet	7,000,000

If you use:

=INDEX(C2:C4,2)

Excel returns:

10,000,000

This is because 10,000,000 is the second value in the range C2:C4.

What Is the MATCH Function in Excel?

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

Its syntax is:

=MATCH(lookup_value, lookup_array, [match_type])

Where:

lookup_value is the value you want to find.
lookup_array is the range where Excel should search.
match_type determines how Excel performs the match.

For an exact match, the most commonly used option is:

For example:

=MATCH("SP02",A2:A4,0)

The result is:

This means that SP02 is the second item in the range A2:A4.

How to Combine INDEX and MATCH

The real power of these functions comes from using them together.

Suppose you have this table:

Product ID	Product	Price
SP01	Laptop	15,000,000
SP02	Smartphone	10,000,000
SP03	Tablet	7,000,000
SP04	Monitor	5,000,000

Suppose cell E2 contains the product ID you want to search for:

SP03

To return the corresponding price, use:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

The formula works in two stages.

First, MATCH finds the position of SP03:

=MATCH(E2,A2:A5,0)

The result is:

INDEX then returns the third value from C2:C5:

=INDEX(C2:C5,3)

The final result is:

7,000,000

In other words, the formula:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

uses MATCH to locate the correct row and INDEX to return the corresponding value.

Why Use INDEX MATCH Instead of VLOOKUP?

INDEX MATCH has several advantages over VLOOKUP, especially when working with more complex spreadsheets.

For example, VLOOKUP might look like this:

=VLOOKUP(E2,A2:C5,3,FALSE)

This works well when the lookup column is the first column of the selected range.

However, VLOOKUP has a major limitation: the lookup column generally needs to be positioned to the left of the column containing the result.

INDEX MATCH does not have this limitation.

Looking Up Values to the Left

Consider this table:

Product	Product ID	Price
Laptop	SP01	15,000,000
Smartphone	SP02	10,000,000
Tablet	SP03	7,000,000

If E2 contains SP03 and you want to return the product name, you can use:

=INDEX(A2:A4,MATCH(E2,B2:B4,0))

The result is:

Tablet

Here, MATCH searches the Product ID column, while INDEX returns the corresponding product name from the column to its left.

This is one of the most useful advantages of INDEX MATCH.

Using INDEX MATCH with Multiple Criteria

INDEX MATCH can also be used to perform lookups based on more than one condition.

For example:

Employee	Product	Sales
Anna	Laptop	20,000,000
Brian	Smartphone	15,000,000
Anna	Smartphone	18,000,000
Brian	Laptop	25,000,000

Suppose:

E2 contains the employee name.
F2 contains the product name.

You can use:

=INDEX(C2:C5,MATCH(1,(A2:A5=E2)*(B2:B5=F2),0))

This formula looks for the row where both conditions are true.

For example, if:

E2 = Anna
F2 = Smartphone

the formula returns:

18,000,000

In newer versions of Excel, this type of array formula can generally be entered normally. Older versions may require Ctrl + Shift + Enter.

Using INDEX MATCH for a Two-Way Lookup

Another powerful application is a two-way lookup, where you need to find a value based on both a row and a column.

Consider this table:

	January	February	March
Laptop	20	25	30
Smartphone	15	18	22
Tablet	10	12	15

To find the sales figure for Smartphone in February, use:

=INDEX(B2:D4,MATCH("Smartphone",A2:A4,0),MATCH("February",B1:D1,0))

The first MATCH identifies the correct row.

The second MATCH identifies the correct column.

INDEX then returns the value at their intersection.

The result is:

This approach is especially useful for financial reports, sales reports, inventory tables, and other spreadsheets where data is organized in rows and columns.

Handling Errors with IFERROR

If the lookup value does not exist, INDEX MATCH returns the #N/A error.

You can use the IFERROR function to display a more user-friendly message:

=IFERROR(INDEX(C2:C5,MATCH(E2,A2:A5,0)),"Not found")

If the value exists, Excel returns the corresponding result.

If there is no match, Excel displays:

Not found

This is particularly useful when creating spreadsheets for other people to use because it makes lookup errors easier to understand.

INDEX MATCH vs. XLOOKUP

If you are using a newer version of Excel, XLOOKUP is often a simpler alternative to INDEX MATCH.

For example:

=XLOOKUP(E2,A2:A5,C2:C5)

The equivalent INDEX MATCH formula is:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

XLOOKUP is easier to read and provides several modern lookup features. However, INDEX MATCH remains valuable because it is widely supported and offers considerable flexibility.

Learning INDEX and MATCH is also useful for understanding how Excel separates the tasks of finding a position and returning a value.

Common INDEX MATCH Formulas

Here are some of the most useful patterns to remember.

Basic lookup

=INDEX(C2:C10,MATCH(E2,A2:A10,0))

Lookup to the left

=INDEX(A2:A10,MATCH(E2,B2:B10,0))

Lookup with two criteria

=INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0))

Two-way lookup

=INDEX(B2:D10,MATCH(G2,A2:A10,0),MATCH(H2,B1:D1,0))

Lookup with error handling

=IFERROR(INDEX(C2:C10,MATCH(E2,A2:A10,0)),"Not found")

When Should You Use INDEX MATCH?

INDEX MATCH is a good choice when you need more flexibility than a basic VLOOKUP formula can provide.

It is especially useful when:

The lookup column is not the first column.
You need to return a value from a column to the left.
You need a two-way lookup.
You are working with complex lookup formulas.
You need to combine multiple criteria.
You want a lookup method that works well with older Excel versions.

For users working with Microsoft 365 or newer versions of Excel, it is also worth learning XLOOKUP, FILTER, and other modern lookup functions. However, INDEX MATCH remains an important Excel skill and is still widely used in existing spreadsheets.

Final Thoughts

INDEX and MATCH are powerful Excel functions that become especially useful when combined. MATCH determines where the data is, while INDEX determines which value to return from that position.

Once you understand this basic principle, you can use INDEX MATCH for simple lookups, left-side lookups, multiple criteria, and two-way data retrieval.

For anyone who works regularly with Excel, mastering INDEX MATCH provides a strong foundation for handling more advanced data lookup and spreadsheet tasks.

Ana.

TECHHOME.INFO

Tìm kiếm Blog này