Forum

Notifications
Clear all

How to calculate values by referring a table and applying multiple conditions.

2 Posts
2 Users
0 Reactions
158 Views
(@nagarjun)
Posts: 1
New Member
Topic starter
 
Hello, I wanted to calculate the values in the column "G" as per the reference table in the attached excel sheet, based on 3 conditions namely Dept, grade and existing value before multiplying the corresponding value in the Existing column "F" with the % indicated in the reference table. I tried many ways but unable to succeed. Kindly help me out.

I am using Office 2013 standard version.

 

Best Regards,

Nagarjun

 
Posted : 07/10/2020 9:43 am
(@purfleet)
Posts: 412
Reputable Member
 

You have used merged cells which will only cause problems in a spreadsheet, i would remove them

Your problem is that the reference table is not in a correct table. However as the rates are the same regardless of the salary so you only need 1 table, which then makes it much easier with a 2 way index and match

=F5*INDEX($K$5:$O$13,MATCH($B5,$J$5:$J$13,0),MATCH($E5,$K$4:$O$4,0))+$F5

 
Posted : 07/10/2020 2:08 pm
Share: