Contains Vlookup Multiple Return

addisonb39

New Member
Joined
Jun 1, 2015
Messages
4
Hi all,

I have a issue that I've been working on for months, and I believe it's just above my pay grade. It seems like it's going to be a index/matching combo.

I have a very general phrase like "3/8 flat washer", and I have a pretty large list I need to return all the hits from that contain "3/8 flat washer from".

I have sent very vague terms all day, and I have to pick their more exact matches out of a list. If I can somehow automatically narrow the list down as far as possible, it would save me tons and tons of shorting. Below of what i'm talking about. I'm not married to this set up what so ever. So ideally it would return either Item ID/Description of 1133815, 33008, and 1133219 since they all contain "3/8 flat washer". The actual list is ~200k lines so this is quite narrowed. Open any and all solutions.

Please please help!


Search:

<tbody>
</tbody>
3/8 flat washerItem IDDescription
Results:#11133815
3/8" Yellow Zinc Finish SAE Thru-Hardened Flat Washer

<tbody>
</tbody>
#233008
3/8" Zinc Finish USS Flat Washer

<tbody>
</tbody>
#31133219
3/8" x 1.250" Zinc Finish Fender Washer

<tbody>
</tbody>
#40115014
1/4"-20 x 2-3/4" Grade 8 Yellow Zinc Finish Hex Cap Screw

<tbody>
</tbody>
#50115059
5/16"-18 x 1-1/2" Grade 8 Yellow Zinc Finish Hex Cap Screw

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Maybe you can use advanced filter. Set up your criteria range and it will filter all entries that contain that phrase. Hope this helps.
 
Upvote 0
if you are using Excel 2010 or later version, turn on Auto Filter, apply filter to "Description", type "3/8*flat washer" (no need to input "") into the search box

 
Upvote 0
if you are using Excel 2010 or later version, turn on Auto Filter, apply filter to "Description", type "3/8*flat washer" (no need to input "") into the search box


Well i get 20-30 of these "descriptions" at a time which are all very different. That's why I was trying to find a formula instead of doing them each individually.
 
Upvote 0
Not sure if I have understood correctly...
In E2
=IF(ISNUMBER(SEARCH("*"&SUBSTITUTE($B$1," ","*")&"*",D2)),"Possible match","")

Search:
3/8 flat washerItem IDDescription
Results:#111338153/8" Yellow Zinc Finish SAE Thru-Hardened Flat Washer
Possible match
#2330083/8" Zinc Finish USS Flat Washer
Possible match
#311332193/8" x 1.250" Zinc Finish Fender Washer
#41150141/4"-20 x 2-3/4" Grade 8 Yellow Zinc Finish Hex Cap Screw
#51150595/16"-18 x 1-1/2" Grade 8 Yellow Zinc Finish Hex

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

However, according to your table, there is only one cell for inputting the search value, you still have to do it many times...




Well i get 20-30 of these "descriptions" at a time which are all very different. That's why I was trying to find a formula instead of doing them each individually.
 
Upvote 0
Not sure if I have understood correctly...
In E2
=IF(ISNUMBER(SEARCH("*"&SUBSTITUTE($B$1," ","*")&"*",D2)),"Possible match","")

Ahhhh... I am subscribing to this :) He could also set up a macro to filter on column E every time the value of B1 changes.... that way it would automatically show only the possible matches each time a user entered into the search "box".
 
Upvote 0
Ahhhh... I am subscribing to this :) He could also set up a macro to filter on column E every time the value of B1 changes.... that way it would automatically show only the possible matches each time a user entered into the search "box".

Well the problem with "possible match" is the list of possible matches is ~200k lines long. Basically what i'm looking to accomplish is: every time the text in B1 changes, I want a list to return every "description" that includes the text from B1. So for my example of "3/8 flat washer" in B1, there would be a "results" column that v-looked up in or sorted through all the 200k "descriptions" and pulled every one that contains the words "3/8 flat washer". So basically doing a vlookup that pulls any result as long as it contains those 3 words.

Did that help at all? Or make it worse?
 
Upvote 0
Mfexcel's solution effectively does that, it just puts the "possible match" result in an extra column, column E. You could easily filter on the value of "possible match" in column E in order to only view the results that the formula finds your search strong in. The filtering action itself could be automated using vba as well.
 
Upvote 0
Actually, I can definitely make that work.

The only sticking point i'm finding, it wont work for >3 "search words". So if I put "3/8 flat washer zinc", it doesn't bring back Possible Match.

Any thoughts? Or is 3 search terms the limitation?

Thank you!!
 
Upvote 0
The number of words shouldn't have anything to do with it. I have tested it with 4-5 words just fine.

Here is a solution for you that will work if you want a "results" area. This will output each part number where it finds there is a "possible match" listed in the "Possible Match" column. Note that you could change this criteria to anything you like in place of "Possible Match", such as "X" but you will need to modify it in the formulas and modify the references to "Possible Match" to "X" or whatever else you choose.

Note that the Results formula is an array which will require Ctrl + Shift + Enter to execute. You can drag it down as many rows as you will need (As many possible parts it could find, so you'll want to go pretty far).

Excel 2010
ABCDEF
1Search3/8" zinc finish washerItem IDDescriptionPossible Match
211338153/8" Yellow Zinc Finish SAE Thru-Hardened Flat WasherPossible match
3Results:Item ID330083/8" Zinc Finish USS Flat WasherPossible match
4113381511332193/8" x 1.250" Zinc Finish Fender WasherPossible match
5330081150141/4"-20 x 2-3/4" Grade 8 Yellow Zinc Finish Hex Cap Screw
611332191150595/16"-18 x 1-1/2" Grade 8 Yellow Zinc Finish Hex

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Vlookup Multiple Results

Worksheet Formulas
CellFormula
F2=IF(ISNUMBER(SEARCH("*"&SUBSTITUTE($B$1," ","*")&"*",E2)),"Possible match","")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B4{=IF(ROWS(D$2:D3)>COUNTIF($F$1:$F$8,"Possible Match"),"",INDEX($D$1:$D$8,SMALL(IF(ISNUMBER(SEARCH("Possible Match",$F$1:$F$8)),ROW($D$1:$D$8)),ROWS(D$2:D3)),1))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top