# How to Use INDEX and MATCH for Powerful Lookups in Excel

Performing lookups in Excel is a common task, but the built-in VLOOKUP function has limitations VLOOKUP can only lookup values to the right, requires sorted data for approximate matches, and has difficulty with two-way lookups. For more flexibility, you can use INDEX and MATCH instead. This combo allows you to lookup values in any direction, doesn’t require sorting for approximate matches, can lookup based on multiple criteria, and handles two-way lookups with ease

In this article I’ll explain how INDEX and MATCH work together to overcome the limitations of VLOOKUP and provide powerful lookup capabilities in Excel.

## Understanding INDEX and MATCH

To use INDEX and MATCH together, it helps to understand what each function does individually:

• INDEX returns a value based on a specified row and column number.
• MATCH returns the relative position of a lookup value in a range.

We can combine these functions so that MATCH provides the position to INDEX, which then returns the value at that location.

This gives us a dynamic lookup that can adapt to any lookup value or range.

## Looking Up a Value Based on a Row Match

As a simple example, let’s lookup a value from a one-column range using INDEX and MATCH.

Given a list of planet names, we want to lookup the diameter of a planet specified in cell A2:

![Planets table][]

The INDEX function needs a range and row number to return a value:` =INDEX(B3:B11,4)`

But rather than hardcode the row number, we can use MATCH to find it dynamically based on the lookup value:` =INDEX(B3:B11,MATCH(A2,A3:A11,0))`

MATCH finds the position of the lookup value in A2 within the range A3:A11, and returns that to INDEX as the row number. INDEX then returns the corresponding value from the range B3:B11.

This lookup formula works for any value specified in A2.

## Two-Way Lookup with Row and Column Matches

To expand on that example, let’s lookup a value based on both a row and column match.

Given a table of sales figures by month:![Sales table][]

We want to lookup the sales for a particular salesperson and month. To do this, we need INDEX to take both a row number and column number:`=INDEX(C3:E11,MATCH(A2,B3:B11,0),MATCH(A3,C2:E2,0))`

The first MATCH provides the row number, finding the position of the salesperson name.

The second MATCH provides the column number, finding the position of the month.

INDEX then returns the value at the intersection of those row and column numbers.

This allows a two-way lookup based on both criteria.

## Performing a Left Lookup

A key advantage of INDEX/MATCH over VLOOKUP is the ability to perform a left lookup: where the return column is to the left of the lookup column.

For example, here is a left lookup that returns the month name based on a sales amount:` =INDEX(C2:E2,1,MATCH(F2,C3:E11,0))`

MATCH finds the relative position of the lookup value and passes that to INDEX as the column number. INDEX returns the month name in that column.

VLOOKUP cannot perform this type of left lookup.

## Lookup with Multiple Criteria

To lookup based on more than one criteria, you can use INDEX/MATCH with Boolean logic:` =INDEX(E3:E9,MATCH(1,((A2=B3:B9)*(C2=C3:C9)),0))`

The MATCH function returns the position of the first exact match based on criteria in both A2 and C2. INDEX then returns the corresponding value.

This requires array entry with Ctrl + Shift + Enter since it’s an array formula.

## Case-Sensitive Lookup

To make a case-sensitive lookup, use the EXACT function: `=INDEX(B3:B11,MATCH(1,EXACT(A2,A3:A11),0))`

EXACT compares the lookup value to each cell, returning an array of TRUE/FALSE. MATCH finds the first occurrence of TRUE.

This also requires array entry with Ctrl + Shift + Enter.

## Finding Closest Matches

You can also find approximate/closest matches with INDEX/MATCH.

Here is a formula that returns the closest match to a lookup value:` =INDEX(B3:B11,MATCH(MIN(ABS(A2-A3:A11)),ABS(A2-A3:A11),0))`

This uses ABS and MIN to find the smallest difference between values. MATCH locates the position of the closest match, and INDEX returns it.

This requires array entry as well.

## Using XMATCH Instead of MATCH

Excel’s newer XMATCH function can be used instead of MATCH for more power and flexibility:

• Default exact match
• Find next larger/smaller match
• Reverse search order
• Faster search with binary lookup

Simply replace MATCH with XMATCH in any lookup formula:`=INDEX(B3:B11,XMATCH(A2,A3:A11))`

## Putting It All Together

As you can see, the flexibility of INDEX and MATCH makes it possible to overcome the limitations of VLOOKUP and perform virtually any type of lookup in Excel.

• Lookup in any direction
• Approximate and exact matches
• Multiple criteria lookups
• Case-sensitive lookups
• Closest match lookups
• Faster formulas with XMATCH

While the examples above use simple data ranges, you can apply INDEX/MATCH to tables, named ranges, entire worksheets, and 3D ranges across multiple worksheets.

So if you need to perform advanced lookups in Excel, INDEX and MATCH should be your go-to functions. With a bit of practice, you’ll be able to craft powerful formulas to handle any lookup challenge.

Frequency of Entities:

VLOOKUP: 6
INDEX: 18
MATCH: 15
XMATCH: 5
Excel: 7

### #2 How to Use the MATCH Formula

Sticking with the same example as above, let’s use MATCH to figure out what row Kevin is in.

• Type “=MATCH(” and link to the cell containing “Kevin”… the name we want to look up.
• Select all the cells in the Name column (including the “Name” header).
• Type zero “0” for an exact match.
• The result is that Kevin is in row “4.”

Use MATCH again to figure out what column Height is in.

• Type “=MATCH(” and link to the cell containing “Height”… the criteria we want to look up.
• Select all the cells across the top row of the table.
• Type zero “0” for an exact match.
• The result is that Height is in column “2.”

## What is INDEX MATCH in Excel?

The INDEX MATCH[1] Formula is the combination of two functions in Excel: INDEX[2] and MATCH[3].

=INDEX() returns the value of a cell in a table based on the column and row number.

=MATCH() returns the position of a cell in a row or column.

Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function. To see a video tutorial, check out our free Excel Crash Course.

## How to use Excel Index Match (the right way)

What is the difference between index & match?

INDEX () returns the value of a cell in a table based on the column and row number. =MATCH () returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function.

What is index match in Excel?

INDEX MATCH is a versatile and powerful lookup formula. In this article, we’ll teach you how to do the INDEX and MATCH functions in Microsoft Excel before showing you how to combine them. INDEX retrieves the value of a given location in a range of cells, and MATCH finds the position of an item in a range of cells.

What is an index match formula?

An INDEX MATCH formula uses both the INDEX and MATCH functions. It can look like the following formula. =INDEX (\$B\$2:\$B\$8,MATCH (A12,\$D\$2:\$D\$8,0)) This can look complex and overwhelming when you first see it! To understand how the formula works, we’ll start from the inside and learn the MATCH function first. Then I’ll explain how INDEX works.

What is index match VLOOKUP?

In an INDEX MATCH function, nested MATCH functions find the row and column numbers for the INDEX function. INDEX MATCH outweighs VLOOKUP for a few reasons, the biggest being it can look to columns on the left. INDEX retrieves the value of a given location in a range.