Need Help with a Formula for solar pump system sizing sheet

[HEM79]

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]I’m trying to configure a solar pump system sizing sheet. Basically, after calculating several criteria, a solar pump model should be automatically selected. The criteria for selection are: required power and required head (m).[/FONT]
[FONT=&quot]I need to extract the right model from a table that compiles all pump models, their power, minimum head and maximum head.[/FONT]
[FONT=&quot]The function should go as following: look up required power and head, choose the model that:[/FONT]
[FONT=&quot]1- has power equal to or more required power;[/FONT]
[FONT=&quot]2- and minimum head that is equal to or more the required head;[/FONT]
[FONT=&quot]3- and maximum head that is equal to or less than required head.[/FONT]
[FONT=&quot]I kept playing with VLOOKUP with IF/AND, Index/Match but failed to find the right forumla. Please help![/FONT]
[FONT=&quot]I’m using EXcel 2016 and Windows 10[/FONT]

 

[etaf]

do you have a sample you can put on a share like dropbox or onedrive

 

[HEM79]

do you have a sample you can put on a share like dropbox or onedrive

Yes.
please follow this link to download a sample from google drive.

https://goo.gl/oydj3t

 

[Taul]

Hi,
In cell G5, try

=IFERROR(INDEX(D$2:D$69,MATCH(1,INDEX((A$2:A$69>=G3)*(B$2:B$69<=G4)*($C$2:$C$69>=G4),),FALSE)),"not in range")

I assume you have the list in cost order, as I have a feeling there will be more than one item that meets the criteria

Paul.

 

[EssoExplJoe]

No comment on the VBA code but the Engineering doesn't make sense. All you should need is a required minimum head and minimum flow rate as this head. The selection would be the pump which outputs a flow rate at a head greater than the minimum head. You shouldn't need to input the power required. Be aware that the friction in the output line and input line adds to the minimum head required, i.e., 4 elbows equals a bull plug!

 

[HEM79]

@Paul, many thanks for the formula. It works! Yes, there will be further ordering and refining of vlookup value based on several other factors such as flow rate, voltage, current, shaft power, etc. But I can now just build on this formula to do what I want so much appreciated.

@EssExpIJoe, thanks for your comment. The engineering makes perfect sense from solar pumping point of view. When you design a solar pump system, directly driven by PV array, you rely on daily water output rather than hourly flow rates versus heads and power required is your most important criteria for sizing. Since output power is directly related to the flow rate/ head combination, it is enough either to use flow and head or power and head or power and flow to select the right pump. We chose to select with power and head. The right flow follows automatically.

And offcourse, by head, I mean total dynamic head including static and friction.

This is much more complicated than just sizing a pump buddy. Cheers