Finding a different partial texts anywhere in a column

**madadd** · 03-02-2011, 01:26 PM

Hello,

I feel like Im having a brain fart and this should be really easy.

I've got 2 lists, say A:A and B:B. in column C I want to put an X if ANYWHERE in Column A my text in Column B appears.
I tried =IF(A:A="*B1*","x",""). So, if anywhere in Column A contains B1, "X", else nothing. It doesn't give me an error, but nothing gets displayed when I know there should be an X.

Example
A B C
adgh ej x
oasid fe x
sejbs tz
ofep lm
bdfej ad x

**NBVC** · 03-02-2011, 01:32 PM

Try:

=IF(COUNTIF(A:A,"*"&B1&"*"),"x","")

or

=IF(ISNUMBER(MATCH("*"&B1&"*",A:A,0)),"x","")

copied down

**madadd** · 03-02-2011, 01:39 PM

Wow, thank you again. Both of those worked like a charm. I still don't understand what I did wrong. After looking at yours, I thought maybe changing my original to:

=IF(A:A="*"&B1&"*","x","")

would work, but no, no it doesn't.

**NBVC** · 03-02-2011, 01:56 PM

By referencing A:A in =IF(A:A="*"&B1&"*","x","") you are not actually referencing the whole column, it just points to A1. Also, the wildcards don't work with formulas using = to evaluate a condition...