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