# INDEX and Match in Excel

How to use INDEX and Match together in Excel.

Firstly we see the syntax of Index and match functions.

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

1. Daniel says: