Before I explain the INDEX and MATCH functions of Excel individually, let’s take an example. Because as the great Iron Man said, “Sometimes you gotta run before you can walk”.
In this example, I have a table of students. Column A has students name, column B has Roll No, and column C has scores.
Now, we need a lookup for the name of roll no 104 as shown in the image below.
If we wanted to look up the score, we would have used Vlookup. Why not here?
VLOOKUP only retrieves data from the left of the table. It can’t do anything for the columns on left.
So to lookup value from the left of the table in excel we will use Excel INDEX MATCH.
In cell G2, I have written an INDEX MATCH formula to retrieve data from left of Column.
The Generic Formula of INDEX MATCH for the lookup would be
This works. How? Come, let’s understand.
INDEX MATCH Explanation:
To understand the above formula, we need to understand the INDEX function and MATCH function individually.
INDEX function in Excel
INDEX simply returns the value from a given range at a defined index number.
The INDEX Function:
For example, from our student table, if I want to get 4th value from range A2:A10, I will write:
It will return “Kavita” which is the 4th value in a given range. Here row number is hardcoded.
Is it hitting you? Yeah! Now we just need to get that index number of “Kavita” by a formula and then it will work. Now MATCH has come into action.
MATCH Function in Excel
The MATCH function simply returns the index number for the first appearance of a value in a range.
Generic MATCH formula:
For example, from our student table, if I want to get index or say row number of 104, I will write:
This will return 4. Now think about it…
Yes, we can use the MATCH function to give the INDEX function its index variable.
Using INDEX - MATCH
We already used INDEX-MATCH formula at the beginning of this article. Let’s understand that:
The MATCH function looks for index number of value in F2 (104) in range B2:B10 and returns 4.
Now we have INDEX(A2:A10,4). The next INDEX function goes to the 4th index in Range A2:A10 and returns its value. Which is “Kavita”.
And it’s done. Simple Isn’t it? Many times people get afraid of the INDEX MATCH function. They say it is too hard but in reality, it is way simpler than the VLOOKUP Formula.
One topic still remains. What is the difference between VLOOKUP and INDEX MATCH. Let’s cover it up quickly.
VLOOKUP vs INDEX MATCH
|VLOOKUP can’t retrieve data from left of table||INDEX MATCH can retrieve data from the left of the table|
|VLOOKUP needs the whole table for reference||INDEX MATCH only needs two columns pf the table|
|VLOOKUP requires the column number from which you want to retrieve the data. Thus you need to count columns manually first or take help from another function.||INDEX MATCH does not need a column number. Hence less human effort is needed.|
In this example, we have seen an index working with one dimension. However, it also works in two dimensions. If you select a 2D range then you can define which column you want to get value from.
For example: INDEX(A2:C10,4,3) will return the 4th value from the third column, which is 51 in our example.
The third argument (match type) supports 2 more options, 1 for less than and -1 for greater than. I am not explaining them here. Try them out by yourself.
Was this article helpful or are you still having difficulties using the INDEX MATCH function in excel, explain it in the comments section. I will be happy to help.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.