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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sentence[/TD]
[TD]Formula[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]You have deleted the number in your 8PAX list.[/TD]
[TD]IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A2)))>0,"Yes","")[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]You have deleted the number in your TEMAN list.[/TD]
[TD]IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A3)))>0,"Yes","")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sorry, you have reached the limit in your TEMAN list.[/TD]
[TD]IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A4)))>0,"Yes","")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sorry, you have reached the limit in your FRIEND's list.[/TD]
[TD]IF(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A5)))>0,"Yes","")[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Eric W]

I've tested your formula, and it seems to work fine for me. 2 thoughts: Your named range is IncludeMe, but in the formula, you use Include. Second, if your named range has empty cells, then your formula will match anything. Either fill empty cells with xxxxxx (or something), or make it dynamic so there are no empty cells to check. Also, with "you" in your named range, it should return yes for all of your examples.

Hope this helps.

 

[cerfani]

what range is the named range "Include"?

is this multiple cells or a single cell?

if search returns 0 then it simply is not there in the referenced range... not sure if things break if Include is referencing multiple cells and maybe you only refer to the first cell in the range if you dont set it up as an array formula

 

[Aladin Akyurek]

In B5 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
    SEARCH(" "&Include&" "," "&TRIM(SUBSTITUTE($A5,","," "))&" "))),"yes","no")

<strike></strike>

 

[cerfani]

In B5 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
    SEARCH(" "&Include&" "," "&TRIM(SUBSTITUTE($A5,","," "))&" "))),"yes","no")

<strike></strike>

what is this doing essentially? why the lookup?

 

[Joe4]

Cross-posted: Search string "TEMAN" in namedrange but return blank?

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[dellzy]

Hi Aladin Akyurek,

Thank you for your formula. However, it has only resolve the one with the "TEMAN" word but it has affected others to be returned as "no". I finally found my culprit.

My latest formula was like this :
=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A2)))>0,SUMPRODUCT(--ISNUMBER(FIND(Exclude,$A2)))=0),"MT","")

My culprit was that, in the namedrange Exclude, there is a string "EMA", which is part of the word "TEMAN", so that's why it return blank. So, I had to put a space after the word "EMA" becoming "EMA " to allow the formula to identify as 2 different strings and will return "Yes".
*phewwww* had spent 3 days thinking what went wrong and the solution is just a space.. lol!

However, thanks a lot for all those who contributed.

DZ

 

[Aladin Akyurek]

Hi Aladin Akyurek,

Thank you for your formula. However, it has only resolve the one with the "TEMAN" word but it has affected others to be returned as "no". I finally found my culprit.

My latest formula was like this :
=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH(Include,$A2)))>0,SUMPRODUCT(--ISNUMBER(FIND(Exclude,$A2)))=0),"MT","")

My culprit was that, in the namedrange Exclude, there is a string "EMA", which is part of the word "TEMAN", so that's why it return blank. So, I had to put a space after the word "EMA" becoming "EMA " to allow the formula to identify as 2 different strings and will return "Yes".
*phewwww* had spent 3 days thinking what went wrong and the solution is just a space.. lol!

However, thanks a lot for all those who contributed.

DZ

Care to post the contents of Include and Exclude? And the contents of A2?

 

[dellzy]

Sure Aladin Akyurek. Kinda long list though..

Include:-
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Teman
[/TD]
[/TR]
[TR]
[TD="class: td1"]U
[/TD]
[/TR]
[TR]
[TD="class: td1"]anda
[/TD]
[/TR]
[TR]
[TD="class: td1"]tidak
[/TD]
[/TR]
[TR]
[TD="class: td1"]enjoy
[/TD]
[/TR]
[TR]
[TD="class: td1"]Please
[/TD]
[/TR]
[TR]
[TD="class: td1"]Ur
[/TD]
[/TR]
[TR]
[TD="class: td1"]free
[/TD]
[/TR]
[TR]
[TD="class: td1"]welcome
[/TD]
[/TR]
[TR]
[TD="class: td1"]Yr
[/TD]
[/TR]
[TR]
[TD="class: td1"]Tempoh
[/TD]
[/TR]
[TR]
[TD="class: td1"]Own
[/TD]
[/TR]
[TR]
[TD="class: td1"]only
[/TD]
[/TR]
[TR]
[TD="class: td1"]subscribe
[/TD]
[/TR]
[TR]
[TD="class: td1"]thank
[/TD]
[/TR]
[TR]
[TD="class: td1"]terima kasih
[/TD]
[/TR]
[TR]
[TD="class: td1"]tahniah
[/TD]
[/TR]
[TR]
[TD="class: td1"]percuma
[/TD]
[/TR]
[TR]
[TD="class: td1"]frenz
[/TD]
[/TR]
[TR]
[TD="class: td1"]family
[/TD]
[/TR]
[TR]
[TD="class: td1"]suara
[/TD]
[/TR]
[TR]
[TD="class: td1"]video
[/TD]
[/TR]
[TR]
[TD="class: td1"]panggilan
[/TD]
[/TR]
[TR]
[TD="class: td1"]pggln
[/TD]
[/TR]
[TR]
[TD="class: td1"]sorry
[/TD]
[/TR]
[TR]
[TD="class: td1"]sila
[/TD]
[/TR]
[TR]
[TD="class: td1"]hanya
[/TD]
[/TR]
[TR]
[TD="class: td1"]senarai
[/TD]
[/TR]
[TR]
[TD="class: td1"]maaf
[/TD]
[/TR]
[TR]
[TD="class: td1"]frenz
[/TD]
[/TR]
[TR]
[TD="class: td1"]calls
[/TD]
[/TR]
[TR]
[TD="class: td1"]best
[/TD]
[/TR]
[TR]
[TD="class: td1"]didaftarkan
[/TD]
[/TR]
[TR]
[TD="class: td1"]details
[/TD]
[/TR]
[TR]
[TD="class: td1"]that
[/TD]
[/TR]
[TR]
[TD="class: td1"]visit
[/TD]
[/TR]
[TR]
[TD="class: td1"]You
[/TD]
[/TR]
[TR]
[TD="class: td1"]Kawan
[/TD]
[/TR]
[TR]
[TD="class: td1"]List
[/TD]
[/TR]
[TR]
[TD="class: td1"]untuk
[/TD]
[/TR]
</tbody>[/TABLE]

Exclude:-
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]BSS
[/TD]
[/TR]
[TR]
[TD="class: td1"]NGIN
[/TD]
[/TR]
[TR]
[TD="class: td1"]Kenan
[/TD]
[/TR]
[TR]
[TD="class: td1"]EMA
[/TD]
[/TR]
[TR]
[TD="class: td1"]QNS
[/TD]
[/TR]
[TR]
[TD="class: td1"]null
[/TD]
[/TR]
[TR]
[TD="class: td1"]VW
[/TD]
[/TR]
[TR]
[TD="class: td1"]SIF
[/TD]
[/TR]
[TR]
[TD="class: td1"]Not
[/TD]
[/TR]
[TR]
[TD="class: td1"]FRENZSMS
[/TD]
[/TR]
[TR]
[TD="class: td1"]${
[/TD]
[/TR]
[TR]
[TD="class: td1"],,
[/TD]
[/TR]
[TR]
[TD="class: td1"]]:
[/TD]
[/TR]
[TR]
[TD="class: td1"]\/
[/TD]
[/TR]
[TR]
[TD="class: td1"]_outgoing
[/TD]
[/TR]
[TR]
[TD="class: td1"]|
[/TD]
[/TR]
[TR]
[TD="class: td1"]==
[/TD]
[/TR]
[TR]
[TD="class: td1"]cgi?
[/TD]
[/TR]
[TR]
[TD="class: td1"]errcode
[/TD]
[/TR]
[TR]
[TD="class: td1"]()
[/TD]
[/TR]
[TR]
[TD="class: td1"]OLNOS
[/TD]
[/TR]
[TR]
[TD="class: td1"]smart2
[/TD]
[/TR]
[TR]
[TD="class: td1"]sql
[/TD]
[/TR]
[TR]
[TD="class: td1"] test
[/TD]
[/TR]
[TR]
[TD="class: td1"]timeout
[/TD]
[/TR]
[TR]
[TD="class: td1"]http://10.1
[/TD]
[/TR]
[TR]
[TD="class: td1"]PORTOUT
[/TD]
[/TR]
[TR]
[TD="class: td1"]UCP28882
[/TD]
[/TR]
</tbody>[/TABLE]


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


Thanks.
DZ

 

[Aladin Akyurek]

Sure Aladin Akyurek. Kinda long list though..
[...]

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?