Every Excel user who loves VLOOKUP has this secret pain inside: You can’t use it for advanced lookups.
Yes, it’s a pain.
No doubt, VLOOKUP is one the most popular lookup functions and you should learn it. But, when it comes to power and flexibility it has some shortfalls (as I mentioned) which you can’t cover up even if you combine it with other functions.
So, these shortfalls gave birth to the combination of INDEX and MATCH which is the most power formula to perform advanced lookups.
This combo formula of INDEX MATCH is perfect for advanced lookups and it’s so flexible that you can lookup for a value in any direction. And, when you replace it with VLOOKUP, you get more power as well.
Both INDEX and MATCH are two different functions which are meant to do different things but when you combine both of them you get one of the most powerful tools for lookup.
Here are some reasons why learning INDEX and MATCH is important:
To get some real facts, I had a word with some of the experts in excel community and tried to learn what are their views about INDEX and MATCH comparing to VLOOKUP.
I am sure these are enough reasons for you to shift to INDEX MATCH from VLOOKUP.
And today, when you finish this guide you will an advanced INDEX MATCH user. I have listed all the stuff you need to master it. And this guide is the biggest thing ever written on these two functions.
I have mentioned 14 different (Basic + Advance) examples which can help you to use it to solve different problems.
So let’s get started.
But, when you come to an elite category of users, and you are concerned about performance, flexibility, and power, this combination of index and match should be your first choice.
Ahead I have listed some points which will help you to make a better comparison on why you should use INDEX MATCH for lookups.
The first and biggest reason for using INDEX and MATCH is you can look up to left or any other direction in your worksheet.
Unlike VLOOKUP, you can choose lookup column and result column independently. And, this ability makes it more powerful and flexible than any other lookup formula.
In the above example, where you need to lookup for the amount of an invoice but with VLOOKUP you can't do that because amount column is on left side.
In VLOOKUP, you need to specify the column number from which you want to return the value. But on the other hand in index and match, you can directly refer to the value column. And, this makes ease to use it.
In above table, you are using VLOOKUP to get the score according to roll number and if you want to use the name instead of roll number you need to change reference as well as col_index_number.
In vlookup, you need to refer to the entire table and you have to specify column index number and when you have to change the reference, you need to change the both.
But, in INDEX and MATCH, wherever you need to change the reference, you just have to drag and drop it to another columns or rows.
In the above table, if you want to lookup with the name instead of roll number you just need to drag and drop the lookup column reference to name column using your mouse.
In term of speed, INDEX/MATCH is far better than VLOOKUP. Charley Kyd from Excel User did a great speed confirmation test.
And, in his test results, he has clearly mentioned that INDEX and MATCH is the top most priority for lookups when it comes to speed.
As I said, in VLOOKUP you need to specify a column index number which is static values.
So, when you insert or delete a column in between the reference table VLOOKUP would give you an error or may be the wrong value.
On the other hand, if you are using INDEX MATCH there is no such kind of problem you have to face.
In above table, I have added a new column between Jan and Feb and before adding that column I had value for March month but now it's for Feb.
But, if you use the combination of index and match, you don't have to worry about these kid of problems.
Before you put together these functions you need to understand them individually. And, here you have a quick intro and syntax details about both.
INDEX function can return a value from a column by specifying the position of the cell. You just need to specify a range and the cell number from which you need to get the value.
INDEX(array, row_num, [column_num])
Let’s say, you 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.
Match Function can help you lookup for a value in a column or in a row and return the position of the cell where that value is.
You just need to specify the column or row from which you want to match the value and the value to match. And, it will return the number of the cell from the column or row in which that value is entered.
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.
To understand the working of INDEX and MATCH, a simple thing you need to pick:
Yes, it’s just as simple.
Just think like this, MATCH function is an undercover agent who will find the criminal and INDEX function is a cop who will arrest that criminal afterward.
But, let’s learn this in detail how we can combine these two functions to replace VLOOKUP.
Below is the syntax of index function.
INDEX(array, row_num, [column_num])
In index function, row_num argument tells 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.
You need to replace match function for row_argument of index. When you use the match, it will look up for the value from the lookup column and return the cell number of that value.
And then, that number will be used by index to figure out the position of the cell from the return value column.
In the end, Index function will return the value from that cell and you will get the value you are looking for.
Nut now, let’s work with a real example.
Below you have a list of cities and name of the employees who worked there. Download this sample file from here to follow along.
Here you 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 your vlookup column and the column where you have employees name, that is your value return column.
And, the formula will be:
Let’s break it down this formula in two part to make it clear.
Part-1: In part one, you 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 part two, you have used index function 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.
The whole power of INDEX MATCH is in this simple thing.
Here you have a list of some examples in which we have used INDEX and MATCH to solver real life problems. And, please download sample file from below link for all the examples.
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 you 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. The formula will be:
First of all, match function 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.
Second, index function returns the employee name from the name column using the same cell number.
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.
So let’s call INDEX and MATCH for the rescue. And, the formula will be:
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 number used that number to return the amount by positioning the cell from the amount column.
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 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.
In this formula, you 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.
You can also replace INDEX and MATCH with HLOOKUP.
As you know HLOOKUP is for horizontal look up but you can also use INDEX 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:
In above formula, instead of using match function in row_num argument of index, you 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.
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:
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.
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:
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.
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.
Wildcards are highly useful. You can perform a partial lookup using wildcard characters. And, the best part is, like all other lookup formulas, you can use wildcards with index and match as well.
Just have a look at below list of name where you have both first and last name for employees and their age.
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:
Asterisk is a wild card character which can replace n number of characters. So, when you have used it after the first name it has replaced the last name.
Learn more about wildcard characters from here.
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:
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.
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.
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:
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.
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.
So the formula will be:
Note: This is an array formula, so you need to enter it using ctrl + shift + enter.
In this formula, you have three different arrays to match three different values. And, these arrays return TRUE and FALSE where values are matched.
After that, when you multiply them with each other you get an array something like this.
And match function returns the position of 1 for the array.
In the end, index function returns the price from the price column using the number return by the match.
Quick Tip: If you don’t want to use an array formula then you can u
Let’s say, you have a list in which you have both text and numeric values. And now from this list, you want to get the first numeric value.
For this, you can combine, ISNUMBER function with index/match. ISNUMBER can help you to identify which value is a number and which one is a text.
The formula will be:
You need to enter this formula as an array (using ctrl + shift + enter).
In this formula, ISNUMBER returns an array equals to the length of the list and in this array, you have TRUE for the values which are numbers and FALSE for the rest of the values.
After that, you have used TRUE in match function as a lookup value. So, it returns the position number of first TRUE from the array.
In the end, using that position number index returns the first numeric value.
Let’s think like this. You have a list of values where some of the first cells are blank and you want to get the first non-blank value.
And, you can use this formula to get that first non-blank value.
Important Point: You need to enter this formula as an array.
We need to split this formula into three different parts to understand it wisely.
First of all, you have used ISBLANK function inside the match function to get an array where you have TRUE for blank cells and FALSE for non-blank cells.
Second, match returns the position number of first TRUE from the array returned by ISBLANK. So at this point, you have the cell number of first non-blank value.
Third, index function simply returns the first non-blank value from the list.
Now let’s say from a list of text values you need to count the most frequently occurring text. In the list below, you have names. But, there are few names which are more than once.
So, now you need to get the name which has the highest occurrence in the list. You can use the below below formula which is a combination of MODE, INDEX, and MATCH.
When you insert this formula, it will return “Tamesha” which is the most occurring name.
First of all, match function will compare the entire range of names with itself. And, while doing this, it will return an array where each text will represent its first position.
Let’s takes an example of name “Tamesha” which is our most occurring name in the list. Now, if you look at the list, it has first occurred on 8th cell and after that on the 12th cell.
But, if you look at in the array, for all the positions where we have “Tamesha” it has returned 8 which is its first position.
After that, from the array returned by the match function, mode function will return the most frequent number which is the cell number of the first occurrence of “Tamesha”.
And in the end, index function will return the text by using that cell number.
Now, let’s say, along with looking for a value you also want to create a hyperlink for that value. In this way, you can quickly navigate to the cell where your lookup is in the column.
For example, in the below table, you need to get the age of a person. And, if you create a hyperlink for this value you can easily go to the cell where that value is.
And, for this, we need to use HYPERLINK function + Cell Function with INDEX and MATCH and the formula will be:
Above formula returns the first lookup va
Let’s split this formula into parts to understand it in a better way.
First of all, you have used index and match inside the cell function. And, when you use these both functions within cell function, you get cell reference of the matched value instead of the matched value.
Second, you have concatenated “#” with the cell reference.
Third, you have again used index and match to get the matched value to use as a link text. In this way, you have the matched value as well as the link to the cell where that value is.
There is a huge force of Excel users who are not aware of INDEX MATCH. Even sometimes I got emails from my reader asking me about how can they perform a left lookup. And, when I tell them about this method they always found it useful and more powerful.
I believe that most of the users shift to INDEX and MATCH from VLOOKUP when they need to perform a lookup to left, like my readers.
But, the point is how much you go beyond this one point. And, that is the reason I have compiled this monster guide and listed all these examples.
And, I am sure this will help you to make the best out of this combo of INDEX and MATCH. But do we have more situations where we can use these functions? Yes, of course, we have.
Now, it’s your time to help me out with a new idea where we can use INDEX MATCH combo. Please share with me in the comment section, I would love to hear from you.
And, please don’t forget to share this guide with your friends.