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 names, 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 lookup the score, we would have used VLOOKUP. Why not here?
VLOOKUP only retrieves data from the right of the table. It can’t do anything for the columns on the left.
So to lookup value from the left of the table in excel, we will use Excel INDEX MATCH.
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 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 the row number is hardcoded.
Is it hitting you? Yeah! Now we need to get that index number of “Kavita” using a formula and then it will work. The MATCH function has come into action.
MATCH Function in Excel
The MATCH function 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 an 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). Next, the 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 more straightforward than the VLOOKUP Formula.
One topic 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 the left of the table||INDEX MATCH can fetch data from the left of the table|
|VLOOKUP needs the whole Table for reference||INDEX MATCH only needs two columns of 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 required.|
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.