Searchable Drop Down Lists

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
803
Office Version
  1. 365
Platform
  1. Windows
Hi all. I have successfully created a searchable drop down list:

Here is my formula, which is written into a data validation preparation list: =TRANSPOSE(SORT((FILTER(Table1[Name],ISNUMBER(SEARCH(INPUT!K2,Table1[Name])),"not found"))))

I am trying to make 1 modification. I don't want to have to type in the values in order, so currently if I want the below value to be returned,

2x2 Box F20T12 2L

I can NOT type "Box 2L." If I do this, it will tell me there is no result. Is there a way I can type in any combination of the desired value and have the filter work appropriately?

Many thanks in advance.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
You can use Wildcard *

Please search Box*2L i suppose it will select 2x2 Box F20T12 2L
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
I have tried to provide a searchable list using data validation with * . Please see if this could help you

Book1
CDEFGHIJ
12L*a
2
3Data
4Box 2LID Box 2L 3T a
5ID Box 2L 3T a 
6Punit sga AT 
7Punit sga QT 
8 
9 
Sheet3
Cell Formulas
RangeFormula
G4:G7G4=IF($I$1="","",IFERROR(INDEX($E$4:$E$7,AGGREGATE(15,6,IF(--ISNUMBER(SEARCH($I$1,$E$4:$E$7))=0,FALSE,ISNUMBER(SEARCH($I$1,$E$4:$E$7))*ROW($A$1:$A$4)),ROWS($A$1:A1))),""))
G8:G9G8=IFERROR(INDEX($E$4:$E$7,AGGREGATE(15,6,IF(--ISNUMBER(SEARCH(2,$E$4:$E$7))=0,FALSE,ISNUMBER(SEARCH(2,$E$4:$E$7))*ROW($A$1:$A$4)),ROWS($A$1:A5))),"")
Cells with Data Validation
CellAllowCriteria
I1List=Validation
 

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
803
Office Version
  1. 365
Platform
  1. Windows
You can use Wildcard *

Please search Box*2L i suppose it will select 2x2 Box F20T12 2L

this is perfect! i can type in as much information that I know, separate all strings by the wildcard and it will filter appropriately.

thank you again for your help - very much appreciated!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,168
Members
414,215
Latest member
SRGDZMCS

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
Top