Vacation accrual formula help please

[rrmando]

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

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.

 

[acw]

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]

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]

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