I am able to color/run logic for the column B using COUNTIF function: (I am checking if element B is substring of any element A)
=COUNTIF($A$1:$A$10, "*"&B1&"*")>0
test1a a TRUE
test2b b TRUE
test3cd c TRUE
e g FALSE
f h FALSE
If I want to use this formula opposite way (I need this for color formatting) I would like to do something like this:
=COUNTIF(A1, "*"&($B$1:$B$10)&"*")>0
Is it possibe to apply custom comparation for COUNTIF function
Better example (1,0 is what I could achieve, True/False is what I needed - has been answered below):
test1a a 1 TRUE
test2b xx 0 TRUE
test3cd b 1 FALSE
e g 0 FALSE
f h 0 FALSE
When the criteria argument is an array of values, the formula will return an array. You then have to SUM the result to get the number of matches. eg:
=SUMPRODUCT(COUNTIF(A1,"*"&$B$1:$B$5&"*"))>0
SUMPRODUCT
allow you to do this without entering it with ctrl+shift+enter
If you don't mind the CSE sequence, you can use something like
=SUM(COUNTIF(A1,"*"&$B$1:$B$5&"*")) > 0
or even:
=OR(COUNTIF(A1,"*"&$B$1:$B$5&"*"))
in Excel 0
= FALSE
and the positive integers = TRUE