Searchable Drop Down Lists

[thewiseguy]

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.

 

[CA_Punit]

You can use Wildcard *

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

 

[CA_Punit]

I have tried to provide a searchable list using data validation with * . Please see if this could help you

Book1
	C	D	E	F	G	H	I	J
1							2L*a
2
3			Data
4			Box 2L		ID Box 2L 3T a
5			ID Box 2L 3T a
6			Punit sga AT
7			Punit sga QT
8
9
Sheet3

Cell Formulas
Range	Formula
G4:G7	G4	=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:G9	G8	=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
Cell	Allow	Criteria
I1	List	=Validation

 

[thewiseguy]

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!!