Using Excel formulas to find a substring in cells when condition is met

Question

I want to search for a given sub-string in column D when conditions are met in columns A and B.

A simple countifs as suggested in the question Count if two criteria match - EXCEL formula does not match because I would like the ingredient (Chocolate in my example) to be part of the values and not an exact match like in that question. It is therefore NOT a duplicate question.

Let's say I own a restaurant (I wish :) and I have this:

1. Column A: the category of a dish
2. Column B: the price range of the dish
3. Column C: the name of a dish
4. Column D: a list of ingredients in that dish

I want to search if any of the main course that is expensive contains chocolate (i.e. putting in a cell the value FALSE, "No" or 0). It could look like something like that

I don't want to do this by using VBA but rather using Excel Formulas.

Really appreciate your help.

Thanks

[NOTE]: The difference with the suggested related question is to add * in the matched strings. Although a small difference, it is what made me ask the question in the first place.

Answer 1

CountIfS is your friend here

=COUNTIFS(A:A,"Main",B:B,"Expensive",D:D,"*chocolate*")>0

Answer 2

Enter into E2:

=AND(A2="Main",B2="Expensive",IFERROR(SEARCH("Chocolate",D2)>=0,FALSE))

And drag down.