# Formula for Multiple Variables

#### dannie

##### New Member
Hello,

Thank you in advance if you can help.

I am hoping to do just a formula, but if I have to do a VBA code attached to a button that is fine.

I have a group ages that have 2 sets of variables attached, below is part of the data I am using.

If I enter an age of 41 and Comp of \$60,000, I need the formula to be "=(lower amount/comp amount)*actual comp" "=(14833/50000)*60000" - since 60000 is more than the first option and less than the second (according to chart below).

I can do the formula for any amount, but I was hoping to be able to input an age and a comp amount and have a formula figure out the cont amount.

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 Age Comp Cont 40 50000 13741 40 10000 27481 40 150000 41222 40 210000 57711 41 50000 14833 41 100000 29665 41 150000 44498 41 210000 62297 42 50000 16041 42 100000 32082 42 150000 48123 42 210000 67372

<tbody>
</tbody>

I appreciate any help! Thanks all! If you need further clarification or details let me know!

-D

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Perhaps this meets your needs. It all relies on multiplication of arrays to accomplish the look-ups.

The formula in I2 relies on helper columns G and H. The formula in J2 is a single-cell formula that reports identical results to I2 but is complex.

ABCDEFGHIJ
24050,00013,7414160,00050,00014,83317,80017,800
34010,00027,481
440150,00041,222
540210,00057,711
6
74150,00014,833
841100,00029,665
941150,00044,498
1041210,00062,297
11
124250,00016,041
1342100,00032,082
1442150,00048,123
1542210,00067,372

</tbody>
Sheet5

Worksheet Formulas
CellFormula
I2=F2/G2*H2

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G2{=MAX((E2=A2:A15)*(B2:B15<=F2)*B2:B15)}
H2{=INDEX(C2:C15,MATCH(E2&G2,A2:A15&B2:B15,0))}
J2{=F2/MAX((E2=A2:A15)*(B2:B15<=F2)*B2:B15)*INDEX(C2:C15,MATCH(E2&MAX((E2=A2:A15)*(B2:B15<=F2)*B2:B15),A2:A15&B2:B15,0))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

DRSteele - you are a genius! thank you so much. this is exactly what i was looking! Thank you!
-D

Genius? Hardly (but I'll take the compliment!) You're welcome.

Replies
0
Views
374
Replies
1
Views
405
Replies
6
Views
121
Replies
1
Views
391
Replies
3
Views
463

1,214,285
Messages
6,118,674
Members
448,845
Latest member
MrEbzz

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