• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to allow for two option

Eloise T

Eloise T

Active Member
=IF(AND(H7=30,IFERROR(SEARCH({"*ware*","*FWU*"},J7)>0,0)),"Answer if true")

The above formula works only for the first option in the curly brackets, "ware."
It needs to work for both options, "ware" and "FWU."

Please see attached file if necessary.
 
Use this instead..

=IF(AND(H7=30,MAX(IFERROR(SEARCH({"ware","FWU"},J7),0))>0),"Answer if true")

Press Ctrl+Shift+Enter of course.

The reason why your original formula didn't work:

Search returns matching positions for both ware & FWU, but since only one of them is present in the cells, the return value looks like this:

{#VALUE!,1}

When you check this with >0, and wrap that with IFERROR, you get

{0,TRUE}

Now, since you are using AND() formula, it will give false no matter what (as one of the values is 0.

Please note that in your case, had you pressed Ctrl+shift+Enter on the original formula, you would have seen FALSE for all values.

also note that you do not need *ware* as SEARCH finds any occurrence of ware in the text. No need for wildcarads.

Hope that helps.
 
Thanks for the solution but more importantly the explanation. I knew I was close, but no cigar. :)

If I were using FIND, I would need (wildcards) *ware*, correct?

Maybe I should ask, when are wildcards necessary in these circumstances?
 
non array would be to coerce
=IF(AND(H7=30,OR(ISNUMBER(SEARCH({"ware","FWU"},J7)))),"Answer if true")
 
Eloise T said:
...
If I were using FIND, I would need (wildcards) *ware*, correct?
Click to expand...

No, FIND is case-sensitive. So If you use any wildcard FIND will look for EXACT text.

Maybe I should ask, when are wildcards necessary in these circumstances?
Click to expand...

Not necessary in this circumstances. SEARCH will do a search in the text, so wildcard is not necessary, unless if you have multiple instances of texts.

eg: just imagine you have the following text

ABC FWU DEF POP
HHH GH FWU WWW
PPP HHH POP
LKM FWU GHU POP

...you want to search for: Contains FWU then after POP. So you can use wildcard as;

=SEARCH("FWU*POP",A1)

First & last is the answers.

You can shorten your answer with COUNT,

=IF(AND(H7=30,COUNT(SEARCH({"*ware*","*FWU*"},J7))),"Answer if true")
 
Haseeb A said:
No, FIND is case-sensitive. So If you use any wildcard FIND will look for EXACT text.



Not necessary in this circumstances. SEARCH will do a search in the text, so wildcard is not necessary, unless if you have multiple instances of texts.

eg: just imagine you have the following text

ABC FWU DEF POP
HHH GH FWU WWW
PPP HHH POP
LKM FWU GHU POP

...you want to search for: Contains FWU then after POP. So you can use wildcard as;

=SEARCH("FWU*POP",A1)

First & last is the answers.

You can shorten your answer with COUNT,

=IF(AND(H7=30,COUNT(SEARCH({"*ware*","*FWU*"},J7))),"Answer if true")
Click to expand...

I appreciate the explanation.
Thank you!
 
pike said:
non array would be to coerce
=IF(AND(H7=30,OR(ISNUMBER(SEARCH({"ware","FWU"},J7)))),"Answer if true")
Click to expand...
I see that the non-array method works without SH+CTRL+ENTER. Can you explain why it works without SH+CTRL+ENTER and the other formula requires it? Specifically, why is formula one below an array and formula two not? (Curly brackets added for clarity.)

{=IF(AND(H7=30,MAX(IFERROR(SEARCH({"ware","FWU"},J7),0))>0),"Answer if true")}

=IF(AND(H7=30,COUNT(SEARCH({"ware","FWU"},J7))),"Answer if true")
 
You must log in or register to reply here.
Back
Top