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.
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.
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).
Match 3 Criteria
This technique can be extended to include more criteria. The following would be example of three criteria in an Excel formula.
You don’t need to stop at 3 criteria either. The formula can go on and on. All you have to do is add an additonal multiplication * symbol a bracket then enter the range and criteria. There you go.
The attached Excel file contains both of the above examples.