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.

=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).

Match 3 Criteria

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))

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.
{"ft_lang_label":"__label__en","ft_lang_prob":0.82710433,"math_prob":0.98142153,"size":1425,"snap":"2019-43-2019-47","text_gpt3_token_len":370,"char_repetition_ratio":0.14356087,"word_repetition_ratio":0.008733625,"special_character_ratio":0.28,"punctuation_ratio":0.11146497,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9936057,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-10-22T04:32:22Z\",\"WARC-Record-ID\":\"<urn:uuid:2300da46-2eba-47af-ba24-ea8b2cd65757>\",\"Content-Length\":\"50541\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:16da4f23-fe85-4aab-a9d8-610f0fed24e8>\",\"WARC-Concurrent-To\":\"<urn:uuid:5dee61d7-be0a-4703-88d3-f5edfbb53753>\",\"WARC-IP-Address\":\"198.49.23.144\",\"WARC-Target-URI\":\"https://www.thesmallman.com/match-multiple-text-criteria\",\"WARC-Payload-Digest\":\"sha1:JNIASWKJTHU57U3M7E6KHFH6OJ4MKY3L\",\"WARC-Block-Digest\":\"sha1:KPI3SRCSBNR7VDRNTN2IB47PGNCGQPGO\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-43/CC-MAIN-2019-43_segments_1570987798619.84_warc_CC-MAIN-20191022030805-20191022054305-00425.warc.gz\"}"}