Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

Excel 2010

I have a range with 9 columns. Of these 9 columns 4 are my concern. They are not all adjacent and I much prefer to NOT change their location with respect to the other column as users are accustomed to the order they appear.

I can apply any named ranges and or VBA to obtain a solution.
I could convert this to an Excel Table. The author prefers I don’t but I can. (It has 3 header rows - arrrgh)
I CANNOT order these in any Ascending or Descending order by ANY of these columns.

Columns of interest and criteria are:
SIZE – I wish to return a list of SIZES that meet the criteria
DIM1 = criteria, numeric known via formula, NOT dependent on user selected TYPE
DIM2 <=criteria, numeric known via formula, NOT dependent on user selected TYPE
TYPE = criteria, User Selected

DIM2 example, criteria known via formula = 27.59, must find a DIM2 Less Than or Equal To 27.59.

The only solution I can come up with is, when the user select the TYPE, use the WorksheetChange Event to loop through the rows of data to build a Data Validation list. This is not my first choice since the user could copy & paste multiple TYPE entries and a Target.Count >1 just opens a whole new can of worms.

The file currently has a dependent drop down; user selects TYPE and then selects SIZE but nothing stops them from selecting a Size which has a Dim1 or Dim 2 not meeting criteria.

TIA

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Correction to Criteria:
SIZE – I wish to return a list of SIZES that meet the criteria
DIM1 >= criteria, numeric known via formula, NOT dependent on user selected TYPE
DIM2 <=criteria, numeric known via formula, NOT dependent on user selected TYPE
TYPE = criteria, User Selected

See attached. I saved as xlsx for your security but if you want to enable macros it's find with me.

Sheet “table” ---> the table to search in
Sheet “dropdown” ---> Sample of User Selected Type, known Dim1 & Dim2 and Desired Drop Down list

In E4

=IFERROR(INDEX(ListSize,SMALL(IF((ListType=$A4)*(ListMaxDim1>=dropdown!$B4)*(ListMinDim2<=dropdown!$C4),ROW(table!$A$4:$A$19)-ROW($A$4)+1,""),COLUMNS($E$4:E4))),"")

Enter with Ctrl+Shift+Enter

Copy across (to at least column I) and down

Awesome!

Thanks a million!