Wildcard

[MRomanow]

Can I use a wildcard in a formula looking at numerical values?

F(OR(E20="PBX",F20="SB*",F20="ZE*"),"N","Y"))

This is the fomula looking at SB43 through SB60 and ZE20 through ZE30 and I cannot get it to work. Any ideas??

 

[Aladin Akyurek]

Logical functions like IF, OR, AND, and NOT do not admit wildcards (i.e., * or ?). Try instead...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"PBX","SB","ZE"},E20:F20))),"N","Y")

 

[hatman]

Wildcards don't work in direct string comparisons... they instead are used in Searches and Lookups. Try this instead:

IF(OR(Not(Iserror(Search("PBX",E20))),Not(Iserror(Search("SB",F20))),Not(Iserror(Search("ZE",F20)))),"N","Y")

Edit: Thanx Greg copy and paste strikes again... but I also always forget that Isnumber() will return realiable results in spite of errors, so incorporating NBVC's solution, this would be better written as:

IF(OR(Isnumber(Search("PBX",E20)),Isnumber(Search("SB",F20)),Isnumber(Search("ZE",F20))),"N","Y")

I never can get the {} groupings to work in a way I expect, so I tend to shy away from them... personal preference.

 

[MRomanow]

That worked perfectly. Can you explain what it is doing so when my boss asks me I can sound smart? THANKS!

 

[NBVC]

Try:

=IF(OR(ISNUMBER(FIND("PBX",E20)),ISNUMBER(FIND({"SB","ZE"},F20))),"N","Y")

 

[hatman]

Which solution did you use?

 

[Greg Truby]

Is anyone else having trouble getting Aladin's to work? Or am I just whiffing this and can't see where? It's hitting OK if matching on arg 1 or arg 2, but wants to compare "ZE" against a 3rd cell which is not in E20:F20.

^edit Paul (hatman), I think you misplaced a parenthesis. I think something like so is what you had in mind:<ul>[*]=IF(OR(NOT(ISERROR(SEARCH("PBX",E1))),NOT(ISERROR(SEARCH("SB",F1))),NOT(ISERROR(SEARCH("ZE",F1)))),"N","Y")[/list] Note the change in the OR's. _/edit

 

[Aladin Akyurek]

Is anyone else having trouble getting Aladin's to work? Or am I just whiffing this and can't see where? It's hitting OK if matching on arg 1 or arg 2, but wants to compare "ZE" against a 3rd cell which is not in E20:F20.
...

Book4
	D	E	F	G	H
19
20		PBX	SB60		N
21
Sheet1

H20:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"PBX","SB","ZE"},E20:F20))),"N","Y")

 

[Greg Truby]

Aladin,

Shouldn't H22 be "N"?

book2
	E	F	G	H
20	PBX	abc		N
21	abc	SB43		N
22	abc	ZE30		Y
Sheet1

 

[hatman]

I think this tweak to Aladin's formula will work:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"PBX","SB","ZE"},E20&F20))),"N","Y")

Edit: Though now that I look at it, when working properly, it doesn't care if it finds the search strings occur in E20 or F20. All in all NBVC's solution probably gives the most bang for the buck.