Check if a cell contains any or all of the values of an array

[excelos]

Hello!

I have this table:

A	1​
B	0​
C	1​

I would like to check if a cell contains any of the values/strings of Column1 where Column2 is 1.
In a separate formula, I would like to check if a cell contains all the values/strings of Column1 where Column2 is 1.

Thanks!

 

[Eric W]

Try:

Book4 (version 1).xlsb
	A	B	C	D	E	F
1	A	1		List	Any	All
2	B	0		dog	FALSE	FALSE
3	C	1		cat	TRUE	TRUE
4				cab	TRUE	TRUE
5				banana	TRUE	FALSE
Sheet11

Cell Formulas
Range	Formula
E2:E5	E2	=SUM(ISNUMBER(SEARCH($A$1:$A$3,D2))*$B$1:$B$3)>0
F2:F5	F2	=SUM(ISNUMBER(SEARCH($A$1:$A$3,D2))*$B$1:$B$3)=SUM($B$1:$B$3)