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

=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 simply 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 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:

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

For example, from our student table, if I want to get 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). 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.

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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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