Excel Match Multiple Criteria with Formula

In Excel you may want to match two criteria to return a third condition.  In the following article I will show you how you can use an Index and match formula with multiple criteria to return text to a cell.  This handy Excel non array formula is good when you want to match a number of criteria to return a text value.

Match Criteria Excel

Using the above simple example we have 3 Excel columns, we want to match criteria in column 1 and column 2 to return the data in column 3.  In the above example we want to match;

Car and Garden to return Deck

The Excel formula to achieve this based on the above is as follows.

=INDEX($D$12:$D$14,MATCH(1,INDEX(($B$12:$B$14=E11)*($C$12:$C$14=F11),0),0))

Where Range D12:D14 contains the column information we want to return. Range B12:B14 contains the first criterion which is equal to E11 (Car). Range C12:C14 contains the final criterion which is equal to F11 (Garden).

This technique can be extended to include more criteria.  The following would be example of three criteria in an Excel formula.

=INDEX($E$12:$E$14,MATCH(1,INDEX(($B$12:$B$14=F11)*($C$12:$C$14=G11)*($D$12:$D$14=H11),0),0))

The attached Excel file contains both of the above examples.