Use INDEX and MATCH to Lookup Value

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”.

Example
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.
341
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.

In cell G2, I have written an INDEX MATCH formula to retrieve data from the left of Column.

=INDEX(A2:A10,MATCH(F2,B2:B10,0))

The Generic Formula of INDEX MATCH for the lookup would be

=INDEX (range from where you want to retrieve data,Match(lookup_value,lookup range,0))

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:

=INDEX  (range from where you want to retrieve data, row index, [optional col index])

For example, from our student table, if I want to get 4th value from range A2:A10, I will write:

=INDEX (A2:A10, 4)

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:

=MATCH (lookup value,range, 0 for exact match)

For example, from our student table, if I want to get an index or say row number of 104, I will write:

=MATCH (104,B2:B10,0)

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:

INDEX(A2:A10,MATCH(F2,B2:B10,0)):
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.

PRO NOTES:
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.

Related Articles:

Lookup Top 5 Values with Duplicate Values Using INDEX-MATCH in Excel

VLOOKUP Multiple Values

VLOOKUP with Dynamic Col Index

Use VLOOKUP from Two or More Lookup Tables

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube