# VBA code to calculate amount based on multiple criteria

#### Lisa116

##### New Member
I am thinking that maybe VBA code is what I need to get the formula on the statistic sheet to match the company to the company’s data on the Inputs sheet that the individual is with and use that data. The problem I am having is getting the formula to go from one company’s data to the next on the Inputs sheet as well as on the Calc sheet. On the Statistic sheet, the formula is copied down the page, calculating each individual’s life benefit amount based on the individual’s information and the company’s data on the Inputs sheet. On the Inputs sheet, each company’s information is in columns and on the Calc sheet each company’s information is in two columns going down the page.

Formula in F2 on the Statistic sheet:

=IF(XLOOKUP(C2,Inputs!\$B\$1:\$E\$1,Inputs!\$B\$4:\$E\$4)="Yes",IF(E2="E",IF(Inputs!B3="Flat",Inputs!\$B\$8,IF(D2*Inputs!\$B\$9>Inputs!\$B\$8,Inputs!\$B\$8,D2*Inputs!\$B\$9)),IF(E2<>"E",IF(Inputs!\$B\$3="Flat",Inputs!\$B\$12,IF(D2*Inputs!\$B\$13>Inputs!\$B\$12,Inputs!\$B\$12,D2*Inputs!\$B\$13))))*INDEX(Calc!\$B\$3:\$B\$7,MATCH(IF(AND(Calc!\$A\$3:\$A\$7>B2),MIN(Calc!\$A\$3:\$A\$7),MAX(IF(Calc!\$A\$3:\$A\$7<=B2,Calc!\$A\$3:\$A\$7))),Calc!\$A\$3:\$A\$7,0)),IF(E2="E",IF(Inputs!\$B\$3="Flat",Inputs!\$B\$8,IF(D2*Inputs!\$B\$9>Inputs!\$B\$8,Inputs!\$B\$8,D2*Inputs!\$B\$9)),IF(E2<>"E",IF(Inputs!\$B\$3="Flat",Inputs!\$B\$12,IF(D2*Inputs!\$B\$13>Inputs!\$B\$12,Inputs!\$B\$12,D2*Inputs!\$B\$13)))))

My issue is when the company changes, for instance on C3 below, the company is now on C1 of the Input sheet and in my formula all the “Inputs!B” need to read “Inputs!C” and all the “Calc!B3:B7” need to read “Calc!B10:B14”.

thank you in advance for any help.
Lisa

#### Attachments

• 1620064273180.png
40.6 KB · Views: 6

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Lisa116

##### New Member
Question: Can I enter the formula above into a module and tweek the formula to refer to each entity's information, then on the sheet"statistic" in cell F2 have a formula that matches the entity to the corresponding module which will then run the formula?

Replies
4
Views
48
Replies
2
Views
259
Replies
0
Views
49
Replies
6
Views
105
Replies
3
Views
105

1,136,868
Messages
5,678,224
Members
419,753
Latest member
Vj3006

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