Multiple criteria MATCH

Sometimes, when working on business reports, there is a need to create a formula that contains several criteria for searching a range. When we have only one criterion, it is enough to use a combination of INDEX and MATCH functions. When there are more of them, it is already a bit more complicated situation. However, this is not too complicated either, you will see in the “recipe” that follows…

We have a table with the columns first name, last name and salary. How to find a salary for a given first and last name?

In cells F2 and F3 we will enter the name and surname. In cell F3 it is necessary to enter the formula as a combination of the INDEX and MATCH functions:

=INDEX(A1:C10,MATCH(1,(A1:A10=F2)*(B1:B10=F3),0),3)

The first argument of the INDEX function is the range being searched. Then we specify the MATCH function. As its first argument, we will enter the number 1. This value corresponds to the correct result, and we search for it under two conditions. The first is the column that contains the names, and let’s compare it to the contents of cell F2. The second is the column with surnames, and let’s compare it with the content of F3 cells. We state the conditions in parentheses, and separate them with a multiplication sign, which means that both conditions must be met in order for Excel to find the result. The last argument of the MATCH function is 0, which indicates an exact search. The MATCH function is used to find a row in the table, and as the last argument of the INDEX function we set the number 3, which indicates that third column contains salaries.