If Match Part of Text Within Cell, Then Blank

[SteveC]

Hi,

I'm trying to create an if statement, with no luck.

Col A Col B Col F
Apples value nanas
Bannanas wackos
Coconuts value

Formulas in Col B:
If any of the text in Col A matches another cell with "nanas" or "wackos" in
it (in this case F1 and F2), then leave the cell blank, otherwise X. X being
another formula that returns some random value.

Essentially I'm trying to create a reference list in column F, for as many
references as possible, maybe up to 20, so if any of the values in col A
match any of the text strings in column F, it leaves the cell blank.

I tried, couldn't do it. Saw something with =Countif(A1,"*nanas*") but
couldn't adjust it.

Thanks very much.

[Biff]

Hi!

Try this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(F$1:F$2,A1)))),"",your_other_formula_here)

Copy down as needed.

Biff

"SteveC" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm trying to create an if statement, with no luck.
>
> Col A Col B Col F
> Apples value nanas
> Bannanas wackos
> Coconuts value
>
> Formulas in Col B:
> If any of the text in Col A matches another cell with "nanas" or "wackos"
> in
> it (in this case F1 and F2), then leave the cell blank, otherwise X. X
> being
> another formula that returns some random value.
>
> Essentially I'm trying to create a reference list in column F, for as many
> references as possible, maybe up to 20, so if any of the values in col A
> match any of the text strings in column F, it leaves the cell blank.
>
> I tried, couldn't do it. Saw something with =Countif(A1,"*nanas*") but
> couldn't adjust it.
>
> Thanks very much.

[SteveC]

Super! How do you guys come up with this stuff? I never would have thought
of that. Thanks so much.

[Biff]

You're welcome. Thanks for the feedback!

>How do you guys come up with this stuff?

It's the challenge of problem solving! Believe it or not, it's fun!

Biff

"SteveC" <[email protected]> wrote in message
news:[email protected]...
> Super! How do you guys come up with this stuff? I never would have
> thought
> of that. Thanks so much.
>
>