Returning multiple results (closest match not exact) from a table using multiple criteria

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

https://goo.gl/kLWeJV

Originally Posted by outofthehat

I always prefer to work with range names and helper columns - makes it so much easier to see what's going on...

Try the attached. It should do what you need. Uses a subset of your data.

outofthehat, you are a star! many thanks for making it so simple! I can use this approach for so many excel problems.