software

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,quot;*quot;Categoryquot;*quot;,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(quot;*quot;Categoryquot;*quot;,B2,1)

where the formula will search cell B2 for
quot;*safety*quot;,quot;*intercom*quot;,quot;*vision*quot;, etc... and if it finds a match, it
will return the same data, quot;safetyquot;, quot;intercomquot;, quot;visionquot;, 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
354820ACR Elect. Safety Gear (this is where I want the formula)
72724Aiphone Corp. Intercoms (to search the cell on the left)
970820Air Data Vision systems (to find quot;safetyquot;, quot;intercomquot;, etc)
(and return the match)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 . comgt;,
quot;mikequot; gt; wrote:

gt; Hi everyone,
gt;
gt; I am new to this group and was hoping someone here could help me. I
gt; only know a little bit about excel but I am eager to learn.
gt;
gt; I have a list of categories and a second list with transactions with
gt; manufacturers. I am including a section of both here. I want to run a
gt; vlookup/search function with wildcards and multiple returns.
gt;
gt; =vlookup(B:B,quot;*quot;Categoryquot;*quot;,1,false)
gt;
gt; The formula above doesn't work, but it sort of explains what I want to
gt; do. For example, in the first row of data for manufacturers, I want to
gt; search cell B2 with every Category. Something like,
gt;
gt; =search(quot;*quot;Categoryquot;*quot;,B2,1)
gt;
gt; where the formula will search cell B2 for
gt; quot;*safety*quot;,quot;*intercom*quot;,quot;*vision*quot;, etc... and if it finds a match, it
gt; will return the same data, quot;safetyquot;, quot;intercomquot;, quot;visionquot;, etc.. Maybe
gt; there's an easier way to do this that I can't think of. I appreciate
gt; any help/advice you can offer.
gt;
gt; Category
gt; safety
gt; intercom
gt; vision
gt;
gt; Trans. #manufacturer category
gt; 354820ACR Elect. Safety Gear (this is where I want the formula)
gt; 72724Aiphone Corp. Intercoms (to search the cell on the left)
gt; 970820Air Data Vision systems (to find quot;safetyquot;, quot;intercomquot;, etc)
gt; (and return the match)

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!