# How to use INDEX‌ MATCH Formula in Excel

INDEX MATCH, when combined, can change the approach you use to lookup values in Excel.

Yes, you get it right.

The combination of these two functions can match the gap of all other functions which we use for lookup.

Let me tell you how.

If you love to use VLOOKUP then I’m sure you have this secret pain inside: You can’t use it for advanced lookup (e.g. lookup to left).

Right?

But INDEX and MATCH can do this for you.

This combo formula is so flexible and powerful that you can lookup for a value in any direction.

Well, if you are still not convinced to use it, I have some facts and experts opinion to make it for you.

And after that, this monster guide will show you each and everything about combining and using INDEX with MATCH.

So without any further ado let's get started...

Table of Content

• Quick Intro: INDEX and MATCH
• How INDEX and MATCH Work Together
• Top 14 Examples

## Quick Intro: INDEX and MATCH

Before you put together these functions you need to understand them individually. And, here you have a quick intro and syntax details about both.

### What is INDEX Function

`INDEX(array, row_num, [column_num])`

Let’s say, we have a list of values in a column and you want to get particular value from that list.

The position of the value in the list is 6th row. So, now you need to refer to that column and specify the row which is 6.

And, index function will return that value. It’s is really simple to use but it can solve some complex for you.

### What is MATCH Function

You just need to specify the column or row from which you want to match the value and the value to match.

`MATCH (lookup_value, lookup_array, [match_type])`

Let’s say you have a list of employees names and you want to check for a particular employee that where its name is in the list.

In match function, just need to enter the name in the employee and refer to the column or the row.

And it will return a number which will be the position of cell from column or row.

## Combine INDEX and MATCH Work Together

To understand the working of INDEX and MATCH, a simple thing you need to pick:

• MATCH tells the INDEX the position (cell number) of the value in a column or in a row.
• And, then the INDEX returns the value using that position (cell number).

The whole power of this formula is in these two points.

Just think like this, MATCH function is an undercover agent who finds the criminal and INDEX function is a cop who arrests that criminal afterward.

But, let’s learn this in detail how we can combine these two functions. Below is the syntax of INDEX, as you know.

`INDEX(array, row_num, [column_num])`

In INDEX function, row_num argument tells it, that from which row it has to return the value. Let’s say if you enter 4 it will return value from the 4th row.

To create INDEX MATCH formula we need to replace MATCH with row_argument of INDEX.

When we use MATCH, it looks up for the value from the lookup column and return the cell number of that value.

And then, INDEX use that number to figure out the position of the cell from the value column.

In the end, it returns the value from that cell and you get the value you are looking for.

But now, let’s work with a real example.

Below we have a list of cities and name of the employees who are working there.

Here we need to lookup for the employee name who’s working in Mumbai.

Now, if look at data, in the column where you have cities, that is our lookup column and the column where you have employees name, that is your value column.

And, the formula will be:

`=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)`

Let’s break down this formula into two parts to understand it.

Part-1: In the part one, we have used the match function to lookup for the value “Mumbai” and it has returned the “5” which is the position of the cell in which you have value “Mumbai” in cities column.

Part-2: In the part two, we have used INDEX and referred employees name column for lookup of the value.

Here index function knows that you want value from the 5th cell from the column.

So, it has return “Arjun” in the result.

## INDEX MATCH Examples

Ahead we have some of the common problems which we have solved using INDEX MATCH formula.

### 1. Basic lookup with INDEX MATCH

A normal lookup is one the most important task which you need to do with lookup formulas and INDEX MATCH is perfect for this.

Here we have a data table with employee ID and name. Each ID is unique and you need to lookup for employee’s name with his/her ID.

Let’s say you want to look up for the name of EMP-132. For this, the formula will be:

`=INDEX(name_column,MATCH(emp-id,emp-id_column,0))`

#### ...here's how this formula works

First of all, MATCH matches the emp id in the emp id column and returns the cell number of id for which you are looking for.

Here row number is 6.

After that, INDEX returns the employee name from the name column using the same cell number.

### 2. Lookup to Left

VLOOKUP can’t make it to the left while looking for a value. As I have mentioned, in INDEX and MATCH you can perform a lookup in any direction.

In the below data table, you have invoice number column after the amount column.

So, if you want to look up for the amount of any particular invoice, this in not possible with VLOOKUP.

In VLOOKUP when you select a table, the first column in that table will be the lookup column.

But, here in this table, we need to use the last column of the table as a lookup column.

So, thumb down for VLOOKUP here.

Let’s call INDEX and MATCH for the rescue and the formula will be:

`=INDEX(G2:G14,MATCH(L6,J2:J14,0),0)`

#### ...here's how this formula works

First of all, you have referred to the amount column in index function. This is the column from where we need to get the value.

Second, in the row_number argument of index function, you have used match function and specify the invoice number, referred to the invoice column and used zero for the exact match.

Third, match function returns the cell number of the invoice from the range,

And in the end, INDEX use that number to return the amount by positioning the cell from the amount column.

### 3. Approximate Lookup

Just like VLOOKUP, you can also use INDEX/MATCH for approximate lookup.

Approximate lookup can be useful when the value for which you are looking for is not there on the list and you want to get the closest match for that.

In the below table, you have a list of grades according to the marks.

And, if you want to get the grade for 79, you can use the below formula.

`=INDEX(B2:B6,MATCH(D3,A2:A6,1))`

#### ...here's how this formula works

In this formula, we have used 1 in the match function for the match_type which allows it to perform an approximate lookup.

It returns the first value which is lower than or equals to the lookup value.

For 79, the first lowest value is 75 and for 75 grade is B. That’s why you get B in the result.

### 4. Horizontal LOOKUP

As you know HLOOKUP is for horizontal look up but you can also use INDEX and MATCH for this.

Here in the below data table, you have a horizontal table for monthly sales and you want to get the sales value for “May”.

And the formula will be:

`=INDEX(amount,0,MATCH(lookup_month,months,0))`

#### ...here's how this formula works

In the above formula, instead of using MATCH in row_num argument of index, we have used it in column_num.

And, match returns the column number of May month.

And then INDEX returns the value from the result column according to the position number.

### 5. Two Way Lookup

In a two-way lookup, we need to get a value from a table. Just have look at below table where you have zone wise and product wise sales amount.

Now, if you want to get the sales amount of a product for a particular zone, you need a two-way lookup and for that, you need to use a combination of INDEX MATCH MATCH.

Yes, here you need to use MATCH twice.

In a normal combination of INDEX and MATCH, you use MATCH for the row number but in a two-way lookup, you need to use it for column number as well.

The formulas will be:

`=INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))`

#### ...here's how this formula works

In the above formula, to get sales amount from the table you have used index function and then match function for its row_num and column_num argument.

The match function which is the column_num argument returns 5 as the value Product-D in the 5th row in the range you have referred.

And, the match function which is in the row_num argument returns 2 as the value north zone is in the second column in the range you have referred.

Now with these values, index function has returned the value which is in the 2nd column and 5th row: 1456.

### 6. Case Sensitive

If you face a problem when you have two same values in a list or in a column but in the different text case, you can perform a case-sensitive lookup to look up for the right value.

Let’s have a look at below students list where you have the first name and in the second column you have scores.

And, in first there are names which are same but in different text case.

For example, John Parker and JOHN Mathew.

So let’s say, you want to look up for the marks of “JOHN” not “John“, you can create an exact match lookup with INDEX and MATCH.

And the formula will be:

`=INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))`

#### ...here's how this formula works

Here in this formula, you have used the EXACT function within the match function.

As match function is not able to lookup for a case sensitive value and EXACT is a perfect function for this.

It can compare two values and return TRUE is they are exactly the same (including the case) but you need to enter this formula as an array because you have to compare the entire column with a single value in EXACT.

When you enter it, it will return an array like this.

`=INDEX(Sheet1!\$B\$2:\$B\$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))`

From here, you need use match function so that you can get the position of TRUE from the array.

At this point, you have the cell number (row number) of the value you need to look up.

And, you can use INDEX to that value from column.

Important Point: If you get more than one TRUE with EXACT, the match will just return the number for first TRUE

### 7. With Wildcards with INDEX MATCH

From this list, you need to get the age of a particular employee (Sondra).

But the thing is you just know the first name.

And, if you use asterisk then you can look up for the age of Sondra using the first name.

For this, the formula will be:

`=INDEX(B2:B11,MATCH(“Sondra*”,A2:A11,0),0)`

#### ...here's how this formula works

Asterisk is a wildcard character which can replace n number of characters. So, when you have used it after the first name it has replaced the last name.

### 8. Lowest Value

Let’s say you have a list of students with their scores like below.

And now, from this list, you want to look up for the name of the student who has the lowest score.

For this, you can use MIN function with index and match and the formula will be:

`=INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))`

You have students name in column A and their scores in column B.

So, when you insert this formula in a cell and hit enter, it will return the name of the student with the lowest score i.e. Librada Bastian.

#### Explanation

In this formula, we have three different parts.

In the first part, MIN function returns the lowest score.

After that, in the second part, the match function returns the cell for that lowest score.

In the end, the index function returns the value from the student name column using the same cell position returned by match.

Quick Tip: In the same way, you can also get the name of the student who gets the highest score.

### 9. Top nth score

Now think like this, you have a list of students with their scores in exams and from this list, you want to get the name of the student who got the 2nd highest score.

The thing is you don't know what's the second highest score.

Normally, when you lookup for a value with lookup formulas, you are sure about the value for which you are looking for. But, here you don’t know what’s the second highest score.

So, for this, you can combine large function with index and match. The large function will help you to figure out the 2nd highest value from the range.

And, the formula will be:

`=INDEX(student_names,MATCH(LARGE(score,2),score,0))`

### ...here's how this formula works

In this formula, you have used the large function inside the match function for lookup_value argument. And in the large function, you have mentioned score range and 2 to get the second highest value.

Once the large function returns the second largest value, match function used that value and returns the cell number for that.

And in the end, index function uses that cell number and returns the student name.

### 10. Multiple Criteria

Normally, the combination of index and match is meant to look up for a single value.

And that’s why you use a single range in the match function.

But, sometimes when you hit with data in the real world you need to use multiple criteria to lookup for a value.

Let’s take an example below.

Here you have a list of products with the multiple details like product name, category, and size.

And from this data, you want to get the price for a particular product using all the criteria.