How to Use Index Match function

Share Now

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.

Share Now
November 16, 2019

1 responses on "How to Use Index Match function"

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

Leave a Message

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

Tableau Training in Delhi

All Rights Reserved. EC Analytics Consulting 2014 - 2019.

NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy