HAZEM EL MAHI
New Member
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 item (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!
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 item (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!
