INDEX MATCH combination is an alternative to VLOOKUP. The advantage of this combination lies in its flexibility to return value in any row, where as, VLOOKUP can only return value from right of the column where the values are matched.
INDEX:
This function returns a value in a specified cell. The cell is reference is presented through a row number and a column number. In the example above, column number is optional because our array has only one column number. To make a look up dynamic, we have to use MATCH formula to return a row number depending on a value in a cell I2.
array:
A selection of a range where values are going to be looked up.
row_num:
Row number within the range.
[column_num]:
Optional argument that takes row within the range.
MATCH:
Match formula returns a row (or column) number once it matches a target value in a given range. In an example above, MATCH formula provides row number to INDEX function dynamically based on the value of a target cell.
lookup_value:
A value that is going to be matched. This value can be hard typed or presented as a cell reference such as I2 in an example above.
lookup_array:
A range where the value is going to be matched.
[match_type]:
Although it is given as an optional argument, it is not. For an exact match we must select 0. This argument is similar to [range_lookup] argument for the VLOOKUP function.