Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want Vlookup to list multiple items with the same key? | Excel Worksheet Functions | |||
VLOOKUP - Multiple cells in lookup value | Excel Worksheet Functions | |||
vLookup - display multiple rows | Excel Worksheet Functions | |||
vlookup multiple text rows | Excel Discussion (Misc queries) | |||
Using VLOOKUP with multiple first column matches | Excel Worksheet Functions |