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.

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

<tbody>
</tbody>

 

[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;">

31-12-2018

<tbody>
</tbody>

31-12-201831

<tbody>
</tbody>

31-12-2018

<tbody>
</tbody>

31-12-2018

<tbody>
</tbody>

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