Index Match to replace a Vlookup

[admat]

[FONT=verdana, geneva, lucida, lucida grande, arial, helvetica, sans-serif]Good day,
[/FONT]I have a table that stores rates for NI Contributions called Rates and I have been using a Vlookup to return the Rate based on the employee Salary like this

VLOOKUP(I4,'NIS AND TAX TABLE'!$E$2:$F$18,2)

Now that the rates have been increased (highlighted in the Rates Table attachment), how to get the new rates if I update the current Rate Table using the Effective Date. Thanks to ExcelIsFun's YouTube video

https://www.youtube.com/watch?v=EMDIkP76WkU

I've tried this:

{=INDEX(Rates[EMPLOYEE CONTRIBUTION],MATCH(AGGREGATE(14,6,Rates[EFFECTIVE DATE]/((Rates[MONTHLY SALARY]<[@GROSS])*(Rates[EFFECTIVE DATE]<=[@[PAY PERIOD END]])),1)&[@GROSS],INDEX(Rates[KEY],0),1))}

Although it returns values, I am not getting the correct results.

[TABLE="width: 796"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 796, align: center"]
<tbody>[TR]
[TD]EFFECTIVE DATE
[/TD]
[TD]CLASS
[/TD]
[TD]KEY
[/TD]
[TD]MONTHLY SALARY RANGE
[/TD]
[TD]MONTHLY SALARY
[/TD]
[TD]EMPLOYEE CONTRIBUTION
[/TD]
[TD]EMPLOYEER CONTRIBUTION
[/TD]
[TD]CLASS Z
[/TD]
[/TR]
[TR]
[TD]03/09/2016 [/TD]
[TD]1
[/TD]
[TD]42616867
[/TD]
[TD] 867 - 1472.99
[/TD]
[TD] 867.00
[/TD]
[TD] $ 11.90
[/TD]
[TD] $ 23.80
[/TD]
[TD] $ 1.79
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]2
[/TD]
[TD]426161473
[/TD]
[TD] 1473 - 1949.99
[/TD]
[TD] 1,473.00
[/TD]
[TD] $ 17.40
[/TD]
[TD] $ 34.80
[/TD]
[TD] $ 2.61
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]3
[/TD]
[TD]426161950
[/TD]
[TD] 1950 - 2642.99
[/TD]
[TD] 1,950.00
[/TD]
[TD] $ 23.30
[/TD]
[TD] $ 46.60
[/TD]
[TD] $ 3.50
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]4
[/TD]
[TD]426162643
[/TD]
[TD] 2643 - 3292.99
[/TD]
[TD] 2,643.00
[/TD]
[TD] $ 30.10
[/TD]
[TD] $ 60.20
[/TD]
[TD] $ 4.52
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]5
[/TD]
[TD]426163293
[/TD]
[TD] 3293 - 4029.99
[/TD]
[TD] 3,293.00
[/TD]
[TD] $ 37.20
[/TD]
[TD] $ 74.40
[/TD]
[TD] $ 5.58
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]6
[/TD]
[TD]426164030
[/TD]
[TD] 6030 - 4858.99
[/TD]
[TD] 4,030.00
[/TD]
[TD] $ 45.10
[/TD]
[TD] $ 90.20
[/TD]
[TD] $ 6.77
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]7
[/TD]
[TD]426164853
[/TD]
[TD] 4853 - 5632.99
[/TD]
[TD] 4,853.00
[/TD]
[TD] $ 53.20
[/TD]
[TD] $ 106.40
[/TD]
[TD] $ 7.98
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]8
[/TD]
[TD]426165633
[/TD]
[TD] 5633 - 6456.99
[/TD]
[TD] 5,633.00
[/TD]
[TD] $ 61.40
[/TD]
[TD] $ 122.80
[/TD]
[TD] $ 9.21
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]9
[/TD]
[TD]426166457
[/TD]
[TD] 6457 - 7409.99
[/TD]
[TD] 6,457.00
[/TD]
[TD] $ 70.40
[/TD]
[TD] $ 140.80
[/TD]
[TD] $ 10.56
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]10
[/TD]
[TD]426167410
[/TD]
[TD] 7410 - 8276.99
[/TD]
[TD] 7,410.00
[/TD]
[TD] $ 79.60
[/TD]
[TD] $ 159.20
[/TD]
[TD] $ 11.94
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]11
[/TD]
[TD]426168277
[/TD]
[TD] 8277 - 9272.99
[/TD]
[TD] 8,277.00
[/TD]
[TD] $ 89.10
[/TD]
[TD] $ 178.20
[/TD]
[TD] $ 13.37
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]12
[/TD]
[TD]426169273
[/TD]
[TD] 9273 - 10312.99
[/TD]
[TD] 9,273.00
[/TD]
[TD] $ 99.40
[/TD]
[TD] $ 198.80
[/TD]
[TD] $ 14.91
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]13
[/TD]
[TD]4261610313
[/TD]
[TD] 10313 - 11396.99
[/TD]
[TD] 10,313.00
[/TD]
[TD] $ 110.20
[/TD]
[TD] $ 220.40
[/TD]
[TD] $ 16.53
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]14
[/TD]
[TD]4261611397
[/TD]
[TD] 11397 - 12652.99
[/TD]
[TD] 11,397.00
[/TD]
[TD] $ 122.10
[/TD]
[TD] $ 244.20
[/TD]
[TD] $ 18.32
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]15
[/TD]
[TD]4261612653
[/TD]
[TD] 12653 - 13599.99
[/TD]
[TD] 12,653.00
[/TD]
[TD] $ 133.30
[/TD]
[TD] $ 266.60
[/TD]
[TD] $ 20.00
[/TD]
[/TR]
[TR]
[TD]03/09/2016
[/TD]
[TD]16
[/TD]
[TD]4261613600
[/TD]
[TD] 13600 and over
[/TD]
[TD] 13,600.00
[/TD]
[TD] $ 138.10
[/TD]
[TD] $ 276.20
[/TD]
[TD] $ 20.72
[/TD]
[/TR]
[TR]
[TD]20/12/2018
[/TD]
[TD]8
[/TD]
[TD]434545633
[/TD]
[TD] 5633 - 6456.99
[/TD]
[TD] 5,633.00
[/TD]
[TD] $ 70.00
[/TD]
[TD] $ 140.00
[/TD]
[TD] $ 14.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EXPECTED RESULTS
[/TD]
[TD]PAYROLL DATE
[/TD]
[TD]MONTHLY SALARY
[/TD]
[TD]EMPLOYEE CONTRIBUTION
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31-12-2018
[/TD]
[TD]6000.00
[/TD]
[TD]70.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30-11-2018
[/TD]
[TD]6000.00
[/TD]
[TD]61.40
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Care to state how the expected results are related to the upper table in A:H without a reference to any formula you might have?

 

[admat]

The Expected Results is the EMPLOYEE CONTRIBUTION. If I enter the Payroll Date (31-12-2018) and the Monthly Salary (6000.00) it should return EMPLOYEE CONTRIBUTION=70.00. If Payroll Date (30-11-2018) and the Monthly Salary (6000.00), EMPLOYEE CONTRIBUTION=61.40
****** id="cke_pastebin" style="position: absolute; top: 7.99716px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]31-12-2018[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]31-12-201831[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]31-12-2018[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]31-12-2018[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[admat]

Can anyone please help with this problem.

 

[Aladin Akyurek]

Can anyone please help with this problem.

Care to explain how 70.00 obtains for 30-11-2018 and 6000.00? Also care to explain how 70.00 obtains for 31-12-2018 and 6000.00?

 

[TinaNamdev]

he rates have been increased (highlighted in the Rates Table attachment), how to get the new rates if I update the current Rate Table using the Effective Date. Thanks to ExcelIsFun's YouTube video .

Capitalvraddhi

Capital Vraddhi​

​

 

[admat]

The Table is a set rate. What I need is to return the correct EMPLOYEE CONTRIBUTION based on the Payroll Date and Monthly Salary for example

Payroll Date = 31-12-2018
Monthly Salary = 6000.00
EMPLOYEE CONTRIBUTION=70.00.

If Payroll Date = 30-11-2018
Monthly Salary = 6000.00
EMPLOYEE CONTRIBUTION=61.40

Payroll Date = 31-12-2018
Monthly Salary = 4000.00
EMPLOYEE CONTRIBUTION=37.20

 

[Aladin Akyurek]

But I'm asking how

Payroll Date = 31-12-2018
Monthly Salary = 6000.00
EMPLOYEE CONTRIBUTION=70.00

obtains. That is, describe in words hoe you get 70.00 for 31-12-2018 and 6000.00?

 

[admat]

Before the rate increase I used to use this formula
VLOOKUP(I4,'NIS AND TAX TABLE'!$E$2:$F$18,2)
So if an employee's salary was 6000 he/she would fall in the Class 8 category. If their salary was 6457 but less than 7409.99 they would fall in the Class 9 category and those rates would be applied. Now that the rates have been increased, there are 2 Class 8 rates. The second Class 8 rate would come into effect on 20/12/2018 so everyone being paid after the 20/12/2018 and with a salary between 5633 and 6456.99 will now have to use the second Class 8 rates. The table is a set rate so you just have to lookup what salary range you fall in and at what date you are being paid and apply the rate.

 

[jimrward]

Okay so at the moment if the salary fell into the class 9 category the old rate would apply until you update your table to the new rate for class 9

So we need to find the latest class by date that matches the salary bracket