# Wildcard

#### MRomanow

##### New Member
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??

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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")

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.

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

Try:

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

Which solution did you use?

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.

<sup>edit</sup> 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. <sub>/edit</sub>

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
DEFGH
19
20PBXSB60N
21
Sheet1

H20:

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

Shouldn't H22 be "N"?
book2
EFGH
20PBXabcN
21abcSB43N
22abcZE30Y
Sheet1

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.

Replies
13
Views
193
Replies
5
Views
546
Replies
6
Views
463
Replies
3
Views
438
Replies
2
Views
193

1,217,381
Messages
6,136,230
Members
450,000
Latest member
jgp19

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back