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

INDEX-MATCH Function

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