String "TEMAN" is not recognized in namedrange for SEARCH command?

[dellzy]

Hi Excel Experts,

I came across very weird situation here. Hopefully, somebody can give me solution. I'm trying to create a formula that will search the string(s) in a namedrange and return "Yes" if one of them matches in the sentence of the referred cell and return "" if no match. However, I really don't understand, when it comes to the string "Teman" in the sentence, it will return "" although "Teman" has already been added in the namedrange. Is there something wrong with my formula?

Example:-

namedrange: IncludeMe
Teman
You
sorry

Sentence	Formula	Output
You have deleted the number in your 8PAX list.	IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A2)))>0,"Yes","")	Yes
You have deleted the number in your TEMAN list.	IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A3)))>0,"Yes","")
Sorry, you have reached the limit in your TEMAN list.	IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A4)))>0,"Yes","")
Sorry, you have reached the limit in your FRIEND's list.	IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A5)))>0,"Yes","")	Yes

<tbody>
</tbody>

FYI, when I tested with static string "Teman" in the formula, then it will return the value "Yes" as expected.
eg. IF(ISNUMBER(SEARCH("Teman",$A5))=TRUE,"Yes","").
However, I want to keep it neat in a namedrange because the strings for search is expected to grow up to 100 and the formula needs to run across more than 10k rows of variety of sentences. Also, am not sure if it's only gonna be only "Teman" which gives this type of unexplained result, so, setting a static string each time for each of this similar issue can be very messy.

Appreciate your expertise.

Thank you in advance.
DZ

 

[dellzy]

Thanks for the lists. Just to make sure:

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A2)))>0,SUMPRODUCT(--ISNUMBER(FIND(Exclude,$A2)))=0),"MT","")

will invariably return a blank when A2 contains TEMAN (from Include) and NGIN (from Exclude). Is this result what you are after?

Hi,
Your interpretation for the formula is opposite from the real result. The formula supposedly return "MT" when A2 contains any word from Include and non from Exclude.

 

[Aladin Akyurek]

Hi,
Your interpretation for the formula is opposite from the real result. The formula supposedly return "MT" when A2 contains any word from Include and non from Exclude.

My question/observation was: A2 contains both TEMAN and NGIN.

A2 is: UBAHMIGYESS:$msg="Caj RM5 akan ditolak dr akaun anda utk beralih ke pelan NGIN Xpax Baru. Bonus & 8pax/15pax/Teman20 tidak terdpt dlm pelan ini.Htr MIG YES ke 28882 utk teruskan" if $language eq '2';

The result will be a blank. Is this the intended outcome?