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

[HEM79]

Hi,

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

 

[Fazza]

hello,

please consider a query. just like in a database. so no formulas. defined by SQL of this form,

SELECT comma separated list of the names of the fields you want
FROM NameOfYourDataSource
WHERE required power < = pump power AND required head >= minimum head AND required head <= maximum head AND required flow >= minimum flow AND required flow <= maximum flow

if you set this up as a parameterised query, it can happen automatically - that is without VBA code

BTW, the SQL can be done differently, such as using BETWEEN for the head & flow criteria.

Suggest you google for info on Excel query tables

And for more specific info you might include your Excel version.
And search too for tutorials & examples.
This sort of approach can be improved with VBA.

here is an old (2004) and still current link Parameters in Excel external data queries | Daily Dose of Excel


regards