# Need Help with a Formula for solar pump system sizing sheet

#### HEM79

##### New Member
[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]

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### etaf

##### Well-known Member
do you have a sample you can put on a share like dropbox or onedrive

#### Taul

##### Well-known Member
Hi,
In cell G5, try

Code:
``=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

##### New Member
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

##### New Member
@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

Replies
3
Views
448
Replies
1
Views
716
Replies
1
Views
2K
Replies
1
Views
259
Replies
1
Views
562

1,195,705
Messages
6,011,208
Members
441,595
Latest member
Scooter618

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back