INDEX and Match in Excel

Last modified date

Click here to download the sample workbook used in the below examples.

How to use INDEX and Match together in Excel.

Firstly we see the syntax of Index and match functions.

match function excel
Syntax of INDEX and MATCH function in Excel

If we compare VLOOKUP vs INDEX & MATCH. In INDEX & MATCH, we can do REVERSE V-LOOKUP which is not possible in V-LOOKUP.

INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

  • INDEX=INDEX(array,row_num,[column_num])
  • array: In array argument, we need to give the range which includes both row and column of the required cell.
  • row_num: In Row_number we have to give the required cell row number. (This can be done with match function dynamically)
  • column_num: In Column_number argument, we have to give the required cell column number. (This can be done with match function dynamically)

MATCH: Returns the relative position of an item in an array that matches a specified value in a specified order.

  • MATCH=MATCH(lookup_value,lookup_array,[match_type])
  • lookup_value: In Lookup_value argument, we have to give the cell address from the result which is common in both the tables.
  • lookup_array: In Lookup_array argument, we have to give the range of cells which contains the lookup_value in the source table.
  • [match_type]: In match_type argument we have 3 options.
  • Usually, we use an exact match update 0(zero) (exact match = 0(zero)).
  • The other 2 options in match_type are 1 & -1.
  • If we update 1 (Less than) finds the largest value that is less than or equal to lookup_value. Loookup_array must be placed in ascending order.
  • If we update -1 (Greater than) Find the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.

Task1: In Table2 Using INDEX and MATCH Get Student Name from Table 1

Below Formula used

F11=INDEX($A$9:$C$13,MATCH($E11,$A$9:$A$13,0),MATCH($F$10,$A$9:$C$9,0))

match function excel vlookup function in excel
Examples of INDEX & MATCH (TASK1 & TASK2)

Task2: In Table3 Using INDEX and MATCH Get Hall Ticket no from Table 1

Below Formula Used

F18=INDEX($A$9:$C$13,MATCH($E18,$B$9:$B$13,0),MATCH($F$17,$A$9:$C$9,0))

1 Response