Vacation accrual formula help please

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
Hello all. We are using the following vacation accrual table (the 1st row is service months, the rest of the data is vacation accrual days, i.e. vacation flag 3 we would accrue 10 days for 28 months of employment):

Code:
Vacation 	Service Years					
Flag	12	24	36	48	>49	>85
3	    5	10	10	10	15	15
1	    0	 0	0	 0	 0	 0
2	    5	10	10	10	15	15
20	   0	0	 0	 0	 0	 0
17	   5	5	 10	10	10	15
15	   5	5	 10	10	10	15

I was hoping to use INDEX/MATCH to bring back the appropriate value based on the vacation flag and number of months of each employee (i.e. column a = name, column b = vacation flag, column c = # of months employed, column d = # of days to accrue based on the accrual table), but I cannot figure it out. I am getting confused because the number of service months will be all over the place. I was trying to adapt this formula (generously provided on this forum) but it's way above my head:

=SUMPRODUCT(--(H6>A$3:A$6),H6-A$3:A$6,INDEX(B$3:C$6,0,MATCH(E6,B$1:C$1,0))-INDEX(B$2:C$5,0,MATCH(E6,B$1:C$1,0)))

The references in the formula above are not the same as the current table we are using of course. I was just trying to figure out if something like this would work for our current accrual. Any help will be greatly appreciated. Thank you for your time.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Assume your table is on sheet 2 in the range A1:H8. Change the structure slightly so that:
1) you have an extra column of data before the 12 moths that is all 0
2) >49 becomes 49
3) >85 becomes 86

The second line will then be
Flag,0,12,24,36,48,49,86

With your data in sheet1!A:C with headings in row 1 as described above try the formula

=INDEX(Sheet2!$B$3:$H$8,MATCH(Sheet1!B2,Sheet2!$A$3:$A$8,0),MATCH(C2,Sheet2!$B$2:$H$2,1))

in D2. Copy down as required.

Tony
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
Thank you so much for the reply Tony. This works great. I did not know you could use multiple MATCH with INDEX. Is there a limit to the number of MATCH you can use? Thanks again for your help.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
I dare say there is, but it is probably more related to the length / complexity of the formula you create. The index function only uses 2 direction parameters (row and column) so using a match to determine these parameters is a reasonably common method.


Tony
 

Forum statistics

Threads
1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top