Excel Returning Multiple Results (closest Match Not Exact) From A Table Using Multiple Criteria

[Hazem El Mahi]

This may be a repeated question but I failed to find a workable response anywhere.

I have a table showing pump models, minimum & maximum flow rate, minimum & maximum head, and power in one tab called Submersible_Database (see sample attached).

After calculating required head, flow rate and power in another tab called Sizing (see sample), I want to pull all pump models from the table (named table 7 in sample) matching the following criteria: 1- required power <= pump power 2- required head>= minimum head & <= maximum head 3- required flow>= minimum flow & <=maximum flow

Currently, I'm using the following formula, which returns only 1st match (even when i enter it as array formula). I want all possible matches to be listed (for further refining & calculations to select the most efficient one automatically):

[=IFERROR(INDEX(Submersible_Database!H2:H69,MATCH(1,INDEX((Submersible_Database!G2:G69>=Sizing!G43)(Submersible_Database!F2:F69<=Sizing!G43)(Submersible_Database!B2:B69<=Sizing!G48)*(Submersible_Database!C2:C69>=Sizing!G48),),FALSE)),"not in range. Change water output or head parameters")]

Help please!
I have a sample file on Google drive that explains it all...https://goo.gl/kLWeJV

 

[AmjiBhai]

I want to help you, are you available with test-data excel file(s)?

I will offer you a solution that will create a dynamic drop-down of multiple items based on a given condition.