Excel Index matches multiple criteria rows and columns

Matching is the process or the act of comparing two or more items with similar characteristics. If the two items or objects happen to match, we call that a match. Data values in the excel sheet may also appear to match in that the values in the rows match those in the columns.

When we talk of index matching or index match, we simply mean the type of match that is orderly for instance, if the values match, you should obtain the match either alphabetically or in a descending or ascending order.

Index match of multiple criteria rows and columns means we just use particular criteria to match the rows and columns. The criteria will involve taking the data from its original location to a new location but with the same location name. For example, we can match the names under the names column with the age under the age column into a separate cell but within the same excel sheet.

Here is the generic formula that we are going  to use in this tutorial


To do an index match in excel sheets, the following are the steps involved.

Step 1

From your computer or laptop, open a new blank excel sheet and record some data into it if you are doing this for the first time. If you already have the data consider comparing it with the working scenario below. An example is the one shown below.

Step 2

For us to do an index match of columns and rows with the above data set, we are going to use the following functions; the INDEX function and the MATCH function. The INDEX function does the function of ordering the values while the MATCH function will do the match based on the cell in the formula.

Our formula therefore will look like this; {=INDEX (A2:A7, MATCH (1, (F1=A2:A7)*(F2=B2:B7)*(F3=C2:C7), 0))}. You can also write the formula in cell F1 which is the result cell.

NOTE: This is an array formula. You need to press ctrl+shift+enter in case you are using any other version than Excel 365

Because the formula syntax above is for the first value, that is the region; repeat the entire process with different cell values until you can find the result that matches the one below.