Vlookup with wildcard to return multiple results from text field

[trw1976]

I would like to create a very simple Lookup file that I can share with technicians to help them lookup parts. Basically, My file would take a text entry, say Bearing, go to the mass data sheet, find the word bearing in any cell, then return the value from a specified cell matching that row. Basically, I want it to function just like the search box under the drop down for a filter radio button. as you start typing, results start to refine as you get closer to the right result.

The problem is with VLOOKUP, and or Index Match, I can only return one value. I need to return all values, that have all of the text entered into the criteria box.

In other words, If I type Bearing, only one result comes back with these formulas. I would like a column to populate beneath the text box with all possibilities [TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]LIST[/TD]
[TD]NUMBER[/TD]
[TD]LOOKUP[/TD]
[TD]*BEARING*(FORMULA PASTED DOWN THIS COLUMN WOULD BRING MULTIPLE RESULTS)[/TD]
[/TR]
[TR]
[TD]BEARING,DI,1",FLANGE[/TD]
[TD]12680[/TD]
[TD][/TD]
[TD]12680[/TD]
[/TR]
[TR]
[TD]BEARING;SS,PILLOWBLOCK,1''[/TD]
[TD]12345[/TD]
[TD][/TD]
[TD]12345[/TD]
[/TR]
[TR]
[TD]BEARING;SS,FLANGE,1''[/TD]
[TD]12346[/TD]
[TD][/TD]
[TD]12346[/TD]
[/TR]
[TR]
[TD]MOTOR;400HP,4160VAC[/TD]
[TD]12347[/TD]
[TD][/TD]
[TD]12490[/TD]
[/TR]
[TR]
[TD]CHAIN;ROLLER,40[/TD]
[TD]12355[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPROCKET;15 TOOTH,DI[/TD]
[TD]12378[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BEARING;DI,FLANGE,2''[/TD]
[TD]12490[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VALVE;SOLENOID,24VAC[/TD]
[TD]13579[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like it to work so that if I added say, SS to cell D1 in the example above in additon to the word bearing, it would then filter down to only those numbers with both Bearing, and SS, regardless of the sequence they occur in the list. I.E.... D1=*BEARING**SS*, results would be listed below as 12345 and 12346, but not 12490 OR 12680.

I have tried on another conversation in here, where they were doing something similar, but have had no luck getting it to work on anything but the first cell, or I just receive seemingly random results. Also, in my mass data, the word Bearing, for example, may or may not be first in the nomenclature sequence...

 

[Yongle]

A method using 2 helper columns (C and D), and value to be searched in E1
- helper columns could be hidden after testing

in C2 copied down to last row of data
=ISNUMBER(SEARCH(E$1,A2))*1

in D2 copied down to last row of data
=IF($C2=1,COUNTIF($C$2:$C2,1),"")

in E2 copied down to last row of data
=IFERROR(INDEX($B:$B,MATCH(ROWS(D$2:D2),D:D,0)),"")

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]LIST[/td][td]NUMBER[/td][td]Helper1[/td][td]Helper2[/td][td]

Bearing​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]BEARING,DI,1",FLANGE[/td][td]

12680​

[/td][td]

1​

[/td][td]

1​

[/td][td]

12680​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]BEARING;SS,PILLOWBLOCK,1''[/td][td]

12345​

[/td][td]

1​

[/td][td]

2​

[/td][td]

12345​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]BEARING;SS,FLANGE,1''[/td][td]

12346​

[/td][td]

1​

[/td][td]

3​

[/td][td]

12346​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]MOTOR;400HP,4160VAC[/td][td]

12347​

[/td][td]

0​

[/td][td][/td][td]

12490​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]CHAIN;ROLLER,40[/td][td]

12355​

[/td][td]

0​

[/td][td][/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]SPROCKET;15 TOOTH,DI[/td][td]

12378​

[/td][td]

0​

[/td][td][/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]BEARING;DI,FLANGE,2''[/td][td]

12490​

[/td][td]

1​

[/td][td]

4​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]VALVE;SOLENOID,24VAC[/td][td]

13579​

[/td][td]

0​

[/td][td][/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[trw1976]

My search data actually resides on another sheet on sharepoint that is updated weekly, would I need the helper columns in the search data, or could I place the helper columns still on the sheet with the search tool?

I can try both ways. Will let you know how it goes...

 

[trw1976]

It works well, but is slow, and I do not believe it is refining the search when multiple criteria is input. If I put BEARING, I get 772 results from mass data. If I put *Bearing**1*, I get 772 results.

Also, before I get down the page with 90,000 lines, Excel faults out due to too much data.

 

[trw1976]

I also notice, it is returning things not related to the entered text, I get number 10000047 for example, which has description "PUMP:DIAPH;1 IN RF FLNGD INLET" amongst others...

 

[trw1976]

I believe this is due to the "NG" in FLNGD portion of the text it is finding. I see other examples, and they all seem to have two letters in concession related to the search term entered into what would be E1 in your example.

 

[Yongle]

Bearing????F
= Bearing followed by any 4 characters followed by F
Finds 12346, 12490 in sample data

Bearing*F
= Bearing followed by any number of characters followed by F
Finds 12680, 12346, 12490 in sample data

Further reading on wildcards https://exceljet.net/glossary/wildcard

 

[trw1976]

I was finally able to find a solution to this.

It is excel 2010 Magic Trick 798

This solution works great, and I am able to return a table, to one sheet that's hidden, then put critieria 2 and look at that table, narrow that one to another sheet, then put in my third criteria, which then populates the main search table with a much narrowed down list of possibilities.

The calculations are slow, however, does anyone have a different solution that may be faster?

 

[Yongle]

Using formulas will always be slower that using Data Filter
- a suitable solution for you would be Advanced Filter combined with a small amount of VBA

Follow these instructions to the letter, and then tell me if you think this could be a suitable method for you
- it has been set up in a very simplistic way to pander to the specific needs of advanced
- it can be enhanced later to make it more user friendy (eg you may prefer the input to be in ONE cell)
- it can be enhanced later to increase functionality

1. Copy the data below and paste into a NEW worksheet
- make sure the data is in EXACTLY the same cells
- A1:G13 must be identical

2. Right-Click on sheet tab \ select View Code \ paste the code below into the code window \ go back to Excel with {ALT}{F11}

3. To get an idea how it works ...
Type bear into cell A2 and select any cell in row 3
Type s into cell B2 and select any cell in row 3
Type 1 into cell C2 and select any cell in row 3
Type pill into cell D2 and select any cell in row 3

4, Select any cell in row 1 or row 4 to clear the filter

5. Select any cell in row 2 and the value in that cell is cleared

6. The workbook must be saved as type macro-enabled

The data
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]LIST[/td][td]LIST[/td][td]LIST[/td][td]LIST[/td][td]LIST[/td][td]LIST[/td][td]LIST[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#C6E0B4][/td][td=bgcolor:#C6E0B4][/td][td=bgcolor:#C6E0B4][/td][td=bgcolor:#C6E0B4][/td][td=bgcolor:#C6E0B4][/td][td=bgcolor:#C6E0B4][/td][td=bgcolor:#C6E0B4][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]LIST[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]BEARING,DI,1",FLANGE[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]BEARING;SS,PILLOWBLOCK,1''[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]BEARING;SS,FLANGE,1''[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]MOTOR;400HP,4160VAC[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]CHAIN;ROLLER,40[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]SPROCKET;15 TOOTH,DI[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]BEARING;DI,FLANGE,2''[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]VALVE;SOLENOID,24VAC[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.CountLarge > 1 Then Exit Sub
    Dim critRng As Range: Set critRng = Range("A2:F2")
    Select Case Target.Row
        Case 2:             Target.ClearContents
        Case Is = 1, 5:     Me.ShowAllData: critRng.ClearContents
        Case Else
            Call Wildcard(critRng, True)
            Range("A5").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G2"), Unique:=False
            Call Wildcard(critRng, False)
        End Select
End Sub
Private Sub Wildcard(rng As Range, Yes As Boolean)
    Dim cel As Range
    For Each cel In Range("A2:F2")
        If Not Yes Then
            cel = Replace(cel, "*", "")
        Else
            If Len(cel) > 0 Then cel = "*" & cel & "*"
        End If
    Next
End Sub

 

[Yongle]

Advanced filter is an amazing tool
- can be used without VBA
- but VBA reduces setup time etc
- some suggested reading for you https://www.contextures.com/xladvfilter01.html