Require help on vlookup to match any one of multiple values

Posted on

QUESTION :

I have a column of data, let’s say of food. If the cell says either “pizza” or “coke” or “fries”, then I want it to return TRUE, if not return FALSE. How can I do that? I thought vlookup can only match one value, but I have 3 of them.

ANSWER :

If you want to find whether any of the three words appear in the column of data, you can use the following formula, where A1:A6 is the column of data:

=SUM(COUNTIF($A$1:$A$6,"pizza"),COUNTIF($A$1:$A$6,"coke"),COUNTIF($A$1:$A$6,"fries"))>0

Or, shorter but less transparent:

=SUMPRODUCT(--($A$1:$A$6={"pizza","coke","fries"}))>0