Formula to return a unique Rate basis n number of conditions. Thread Tools Search Thread 8 Minutes Ago #1 andyb16 I agreed to these rules Join Date

[andyb16]

Hi All,

I am trying to build a formula that returns a monthly rate basis a couple of criteria.

I tried using nested if's but something's going wrong.

The rate card below is the basis for my calculation. The logic is: A certain Role against a certain location should return a unique rate.

Rate Card

Offshore Onshore
Developer $10.00 $20.00
Sr Developer $30.00 $40.00
Project Manager $50.00 $60.00
Delivery Lead $70.00 $80.00


For example if I select the role as developer from the Validation and Onshore as location from its validation then formula should return the rate as $20.00.


Rate Role Location
? Developer Onshore

Please help.

Thanks

 

[kryptonian]

best way to go would be to use a match-index combo...

 

[jmthompson]

If you have only 2 Location options:

F-G-H is your rate list

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="">Role</td><td style="">Location</td><td style="">Rate</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">Role</td><td style="">Offshore</td><td style="">Onshore</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">Developer</td><td style="">Offshore</td><td style="text-align: right;">10</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">Developer</td><td style="text-align: right;">10</td><td style="text-align: right;">20</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="">Developer</td><td style="">Onshore</td><td style="text-align: right;">20</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr></tbody></table>

Sheet5​

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" width="85%" cellpadding="2.5px" rules="all"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" width="100%" cellpadding="2.5px" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">C2</th><td style="text-align: left;">=IF(B2="OnShore",VLOOKUP(A2,F:H,3,FALSE),VLOOKUP(A2,F:H,2,FALSE))</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">C3</th><td style="text-align: left;">=IF(B3="OnShore",VLOOKUP(A3,F:H,3,FALSE),VLOOKUP(A3,F:H,2,FALSE))</td></tr></tbody></table></td></tr></tbody></table>

 

[kryptonian]

assuming you have your table with the roles and locations in A1:C5, and your dropdowns for Role in B8 and Location in C8, enter this formula in A8: "=INDEX(A1:C5,MATCH(B8,A1:A5,0),MATCH(C8,A1:C1,0))"

<i'd like to show this here as an excel table but i can't make it work>

 

[andyb16]

Thanks Guys!!
Its working great. I was not aware of the Index function in excel. Guess i have a lot to learn