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>

 

[admat]

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

That's correct.

 

[Aladin Akyurek]

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.

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

Dates are set above in American date notation...

In E21 control+shift+enter, not just enter, and copy across:

=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)))

Note. Rates at rate revisions are assumed to be in ascending order.

 

[admat]

=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)))

Note. Rates at rate revisions are assumed to be in ascending order.

Thanks, but there seems to be a problem with the formula. Also I tried to enter a new rate in the table for example if there were three Class 8 (03/09/2016, 20/12/2018 and lets say 7/1/2019), won't using the MAX ignore the second set of rates? </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)))

 

[Aladin Akyurek]

Thanks, but there seems to be a problem with the formula. Also I tried to enter a new rate in the table for example if there were three Class 8 (03/09/2016, 20/12/2018 and lets say 7/1/2019), won't using the MAX ignore the second set of rates? </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)))

Please do not comment on/discuss the formulas or explain your problems in terms of formulas which do not work or fit them. Just provide an appropriate input and an output that you expect for that input.

 

[admat]

Please do not comment on/discuss the formulas or explain your problems in terms of formulas which do not work or fit them. Just provide an appropriate input and an output that you expect for that input.

I did that in post #7

 

[Aladin Akyurek]

cc

I did that in post #7

Post #15 reads like a comment in the form of a question, while no input is provided along with the appropriate output.

 

[TinaNamdev]

a reference to any formula you might have?

Capital Vraddhi

Capital Vraddhi complaints​

​

 

[admat]

{=INDEX(NIS[EMPLOYEE CONTRIBUTION],MATCH(MAX((NIS[MONTHLY SALARY]<=J13)*NIS[MONTHLY SALARY])*MAX((NIS[EFFECTIVE DATE]<=J14)*NIS[EFFECTIVE DATE]),NIS[MONTHLY SALARY]*NIS[EFFECTIVE DATE],0))}

This is the current formula I have. The problem with it is, if it doesn't have a matching date for the salary range it returns #N/A. So a salary of $6000 will give me the correct results but if i wanted the rate for $8000 after 12/20/2018 it returns #N/A

 

[Aladin Akyurek]

1. If this is the formula I proposed, try to Convert the table to Range and repost the formula.


2. Try to complete "if i wanted the rate for $8000 after 12/20/2018 it returns #N/A" by also saying what value should be returned.


3. If [2] does not apply to the data of post #1 , provide the data where this result must obtain.

 

[admat]

=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)))

Note. Rates at rate revisions are assumed to be in ascending order.

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

Using this formula:

{=INDEX(NIS[EMPLOYEE CONTRIBUTION],MATCH(MAX((NIS[MONTHLY SALARY]<=E21)*NIS[MONTHLY SALARY])*MAX((NIS[EFFECTIVE DATE]<=D21)*NIS[EFFECTIVE DATE]),NIS[MONTHLY SALARY]*NIS[EFFECTIVE DATE],0))}

D21 = 1/1/19
E21 = 6000
F21 = 70

but if

D21 = 1/12/18
E21 = 6000
F21 = 61.40

also if

D21 = 1/1/19
E21 = 10000
F21 = 99.40




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