help with vlookup, wildcards, multiple searches
 

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)

help with vlookup, wildcards, multiple searches
 

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!

In article . com,
"mike" wrote:

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)

help with vlookup, wildcards, multiple searches
 

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!