Search function with results disappearing when criteria is deleted

[mikiel111]

Asking because I think this is only possible with VBA
Generally speaking: I need the results of various search criteria populated in a specific area of my sheet. Once the search criteria is removed the results are also removed so that something else can be searched.

Specifically speaking:
I have all my entries for Material +their characteristics in sheet List1.

In sheet Build3, I require a search utility which lists stuff based on what i entered. Once i remove the search criteria the list shown is removed, this is vital.
The search criteria considers all the below points
1) Any Material containing the word/s in Cell B3 is a positive match. I can settle for it matching just 1 word if it is easier
2) The criteria from E5:L7 (each of these cells i`ll make into a dropdown list so the search to only considers those)
3) The required range in E3:L4 for a specific characteristic. In E3:L3 I`ll put the lower limit and in E4:L4 the upper limit of what`s needed. Example: in E3 = 20 and E4 = 30 means i`m looking for material costing between 20 & 30).

The results would start being populated from cell Z3 downwards. I think it is easier if just the name of the Material is listed here. I`ll just use drag an XLOOKUP to do some maths on all the resulting materials` characteristics. Once search criteria is altered or removed then list changes or empties in the case of all search criteria being removed

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M											X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1																													Per selected weight chosen							per 100
2		Material		Weight chosen	Cost	pallets required	Fuel req to transport	number of trips	number of drivers	Weight	weight + packaging	packaging required														Name	Cost	pallets required	Fuel req to transport	number of trips	number of drivers	Weight	weight + packaging	packaging required	Cost	pallets required	Fuel req to transport	number of trips	number of drivers	Weight	weight + packaging	packaging required
3													> x
4													< x
5					Type	Fn	High/Low	High/Low	Tag 1	Tag 2	Prep Level	Cost Per
6					Pack Size (g)	No of Servings	Serving Size (g)	Per (g)	Location 1	Location 2	Brand
7					flour	milk	VegN	VegT	Soy	Season
8
Build3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B5	Expression	=AND($B$1<>"",OR(ISNUMBER(SEARCH($B$1,$A4:$BB4,1))))	text	NO

Dropbox

www.dropbox.com

 

[mikiel111]

On further research I have discovered that displaying results in a listbox could also be useful as shown in this video. However if so the requirements would change slightly as the point of use cells would bei can copy and paste results elsewhere without adding buttons etc...

see 24:11 of what i mean by displaying results in the search box