INDEX & MATCH Functions for Two-Dimensional Lookup

Hi,

The INDEX function has a very simple job - look in a column of data and return a value from the row that you specify.

The MATCH function also has a simple job - look in a column of data and return the row number of a value that you specify.

We want to use these advantages of INDEX & MATCH functions for Two-Dimensional Lookup as explained in the following example:

We have merit increases table that based on the annual performance rating of the employee and the position of his/her salary in the salary scale, we are aiming to get the merit increase percentage due to each employee by taking the following steps:

Step (1):
Select the Merit percentages only (B2:E6) (Heading of Rows & Columns of the merit Table are not selected) and go to the Formula Tab – Select – Define Name – Type "Merit" as the Name of the Merit percentages, then click OK.



Step (2)
You can see The created Name by selecting Formulas Tab – Name Manager:



Step (3):
Write the following Formula in Cell D10

=INDEX(Merit;MATCH($B10;$A$2:$A$6;0);MATCH($C10;$B$1:$E$1;0))

Copy the above Formula to Cells D11, D12



Attachment: INDEX_MATCH_Function.xlsx

The INDEX function has a very simple job - look in a column of data and return a value from the row that you specify.

This is only the visible and most used part of what INDEX can achieve. It's much more powerful than that