help with vlookup, wildcards, multiple searches

[mike]

Hi everyone,

I am new to this group and was hoping someone here could help me. I
only know a little bit about excel but I am eager to learn.

I have a list of categories and a second list with transactions with
manufacturers. I am including a section of both here. I want to run a
vlookup/search function with wildcards and multiple returns.

=vlookup(B:B,"*"Category"*",1,false)

The formula above doesn't work, but it sort of explains what I want to
do. For example, in the first row of data for manufacturers, I want to
search cell B2 with every Category. Something like,

=search("*"Category"*",B2,1)

where the formula will search cell B2 for
"*safety*","*intercom*","*vision*", etc... and if it finds a match, it
will return the same data, "safety", "intercom", "vision", etc.. Maybe
there's an easier way to do this that I can't think of. I appreciate
any help/advice you can offer.

Category
safety
intercom
vision

Trans. # manufacturer category
354820 ACR Elect. Safety Gear (this is where I want the formula)
72724 Aiphone Corp. Intercoms (to search the cell on the left)
970820 Air Data Vision systems (to find "safety", "intercom", etc)
(and return the match)

 

[Domenic]

Assuming that E2:E4 contains the list of categories, such as 'safety',
'intercom', and 'vision', try...

C2, copied down:

=LOOKUP(9.99999999999999E+307,SEARCH($E$2:$E$4,B2),$E$2:$E$4)

or

=INDEX($E$2:$E$4,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$4,B2)),0))

The latter needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. Also, if you'd like the formula to be case-sensitive, replace
SEARCH with FIND.

Hope this helps!

 

[mike]

Thank you so much for your quick reply! I tried your second suggestion
and it's working like a charm! You've saved me a lot of time! Now
I'll try to look at it to understand what the formula is doing. This
group is awesome!