Multiple wildcards in One IF Statement or LOOKUP

[OscarAGC]

I have BSU's that share a prefix in this format- 7HP, 7AP, and have some letters after that for different departments.

Then I have indepenedent departments with no client prefix in this format- 2HO, 1HO,ZHO, etc.

I want to make an IF statement with multiple wildcards, something like this...

=if(isnumber(search("*7AP*",A1),"AP",search("*7HP*",A1),"HP",search("*2HO*",A1),"HO",search...etc....

I tried a lookup with wildcards, but that didnt work either.


Thanks to all who choose to help!

 

[xenou]

The search formula doesn't need the wildcards operators. It already searches for the text as a substring without those. That might work for you.

Another option is to put the search values in a lookup too... Just had some discussion on this recently:
http://www.mrexcel.com/forum/showthread.php?t=372923&page=2
(see Barry's post #12 and others).

HTH, Alex

 

[Domenic]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH({"7AP","7HP","2HO"},A1),{"AP","HP","HO"})

or

=INDEX({"AP","HP","HO"},MATCH(TRUE,ISNUMBER(SEARCH({"7AP","7HP","2HO"},A1)),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[OscarAGC]

What is the 9.99999999E+307 ?

 

[Domenic]

What is the 9.99999999E+307 ?

Have a look here...

 

[IvenBach]

Here an explanation in the forums http://www.mrexcel.com/forum/showthread.php?t=353007&highlight=9.99999999999999E%2B307.

 

[OscarAGC]

Isn't a lookup function a lot easier?

If it has "*bla*" in it, return "Bla Bla", if it has "*pla*" in it , return "Pla Pla"

That formula looks to be overkill. This can be done really simply with a Lookup function, I'm just having trouble with the syntax.

 

[xenou]

It is overkill - quite a large number. But it is a lookup, and in fact a simple one in its own way.

Maybe you'd prefer this one, with a little less 9's?
=LOOKUP(9E+10,SEARCH({"7AP","7HP","2HO"},A1),{"AP","HP","HO"})

 

[xenou]

You may like to have the lookup values in a small table rather than embedded in the formula - could be easier to maintain also:

book1
	A	B	C	D	E
1	OriginalValue	LookupValue		LookupTable
2	2HO	HO		7AP	AP
3	7HP	HP		7HP	HP
4	2HO	HO		2HO	HO
5	7AP	AP
6
Sheet1

I used Domenic's formula - sans writing a VBA custom function I can't find anything simpler for it.
In Cell B2:
=LOOKUP(9.999999999999E+307,SEARCH(A2,$D$2:$D$4),$E$2:$E$4)