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]

Your formula is incomplete. The last INDEX is followed by an IF with no values to return.

[…]

You could have signaled that much earlier. The < sign, if used in a formula, is interpreted by the board software as a html bit which leads to cutting of the formula...

Here is the full version:

=IF($D21>=MAX($A$2:$A$18),INDEX(IF($A$2:$A$18>=MAX($A$2:$A$18),$F$2:$F$18),MATCH($E21,IF($A$2:$A$18>=MAX($A$2:$A$18),$E$2:$E$18),1)),INDEX(IF($A$2:$A$18 < MAX($A$2:$A$18),$F$2:$F$18),MATCH($E21,IF($A$2:$A$18 < MAX($A$2:$A$18),$E$2:$E$18),1)))

 

[admat]

IF($C27>=MAX($A$5:$A$21),INDEX(IF($A$5:$A$21>=MAX($A$5:$A$21),$E$5:$E$21),MATCH($D27,IF($A$5:$A$21>=MAX($A$5:$A$21),$D$5:$D$21),1)),INDEX(IF($A$5:$A$21 < MAX($A$5:$A$21),$E$5:$E$21),MATCH($D27,IF($A$5:$A$21 < MAX($A$5:$A$21),$D$5:$D$21),1)))

Ok, so here's the results F21 is the results cell. My table have changed the monthly salary is in E5:E21, contribution is in F5:F21

C27 = 12/19/18
D27 = 6000
F21 = 61.40 Good

C27 = 12/20/18
D27 = 6000
F21 = 70.00 Good

C27 = 1/1/18
D27 = 2500
F21 = 23.30 Good

C27 = 1/1/19
D27 = 2500
F21 = #N/A should be 23.30

C27 = 1/1/19
D27 = 10000
F21 = 70 should be 99.40

 

[Aladin Akyurek]

Is post #22 based on your data I reposted in post #12 ?

 

[admat]

Yes the data is the same, I've just removed the Key column.