How to Use Index Match function


Most of the excel user prefer to use VLOOKUP as it is straight forward but there are certain cases where INDEX-MATCH function can work but VLOOKUP can’t. Vlookup can’t look for the data left hand side wise.

The INDEX function

Index function returns the value from the specific position in a table or range.        

Syntax of Index Function

=Index(array, row_num, [column_num])

array = Range of cells or table.

row_num = The number of rows in the array to return the value.

[column_num] = Column number is optional. The number of columns in the table to return the value.

In the above screen shot, Index Function in Cell F2 would return

=Index(B4:F4,3,3) – “Anshul Gupta”(Would return the cell D6 value)


The Match Function:

Match function returns the position of a value in an array.

Syntax of Match Function

=Match(lookup_value, lookup_array, [match_type])

lookup_value = The value you want to find in the list.

lookup_array = List in which you want to find the value.

[match_type] = Match type can be -1, 0 or 1.

-1 finds the smallest value greater than or equal to look up value.

0 finds the exact equal value to lookup value.

1 find the largest value less than or equal to lookup value.

How to use Index Match function in excel

In the above screen shot, Match function would return 3 in the Cell C4. 3rd is the position of Gurgaon (lookup value) in the list B7:B13.


When we combine both the INDEX formula and the MATCH formula, the number that the MATCH formula returns becomes the row number for your INDEX formula.

=INDEX( Array, MATCH Formula)

In the above screenshot combination of INDEX-MATCH function looks for the data to the left which VLOOKUP can’t. In VLOOKUP function lookup value must be in the first column of the array but in INDEX-MATCH function lookup value can be in any column.

December 19, 2012

1 Responses on How to Use Index Match function"

  1. ashish mehra says:

    If you want to know more about “Index Formula vs. Vlookup Formula”, check this link ……..


Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com