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]

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.