Sum array with #VALUE!

[danici]

Hi!
I have to look for specific words inside a text-cell (in the column N) and signal "Yes" if I find any of them.
To do so I used this formula:
=IF(SUM(IFERROR(FIND({"bati";"bateu";"bater";"queimei";"queimou";"queimar";"caiu";"caí";"cai";"cair";"tombei";"tombou";"tombar";"machuquei";"machucou";"machucar"};N:N;1);0))=0;"No";"Yes")

It works for the first word in the array ("bati"), but not for the rest of them, is there any way it can work?

Thanks.

[tim201110]

=IF(SUM(IFERROR(MATCH("*"&{"bati","bateu","bater","queimei","queimou","queimar","caiu","cai","cai","cair","tombei","tombou","tombar","machuquei","machucou","machucar"}&"*",N:N,),))=0,"No","Yes")

[63falcondude]

Try something like this

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"bati";"bateu";"bater";"queimei";"queimou";"queimar";"caiu";"caí";"cai";"cair";"tombei";"tombou";"tombar";"machuquei";"machucou";"machucar"};N1:N100)))>0;"Yes";"No")

Adjusting your range (N1:N100) as necessary.

[tim201110]

Try something like this

not with this array

[63falcondude]

Tim, I'm not sure what you're trying to say. The formula posted in post #3 returns "Yes" if any of the words in the array are found in column N.

Note that I changed the commas to semicolons due to the OP's regional settings.

[xlnitwit]

Hi,

Are you searching one cell, or trying to return YES if any of those words is found anywhere in column N?

[danici]

Thanks, it worked!
what is the purpose of the "--" before the ISNUMBER?

[63falcondude]

Great, happy to help.

ISNUMBER will return TRUE or FALSE. The double unary (--) changes TRUE into 1 and FALSE into 0.

If that solved your question, please mark this thread as SOLVED.