Spreadsheet: 122500d1316864716-help-match-index-formula-for-a-table-matrix-excel-forum-rashid
Sheet: All +$ +YOS DC Rev Days

Name | Annual Salary (AED) | Annual Salary (+5% for 2012 payment) AED | Annual Salary (+5% for 2013 payment) AED | [NAME] as at 31/12/11 | Performance based Accrual on completed YOS as at 31st December 2011 | Actual Y.O.S as at 31/12/11 | Performance based Accrual on Actual YOS as at 31/12/2011 | Y.O.S as at 31/12/12 | Y.O.S as at 31/12/13 | Actual Pro-rata Y.O.S as at 31/12/12 | Actual Pro-rata Y.O.S as at 31/12/13 | Emirati | [NAME] | Total Performance Rating Score | Performance Formula for 2012 | Performance Formula for 2013 | [NAME] to be paid on 31/12/2012 | [NAME] to be paid on 31/12/2013 | Performance Based Method TOTAL | Perf Actual Pro-rata Y.O.S to be paid on 31/12/2012 | Perf Actual Pro-rata Y.O.S to be paid on 31/12/2013 | Performance Based Method Actual Pro-rata Y.O.S TOTAL | Engagement Date | [NAME] for 2012 | [NAME] for 2013 | [NAME] based Accrual on completed YOS as at 31st December 2011 | [NAME] based Accrual on Actual YOS as at 31st December 2011 | [NAME] to be paid on 31/12/2012 | [NAME] to be paid on 31/12/2013 | [NAME] TOTAL | [NAME] Actual Pro-rata Y.O.S to be paid on 31/12/2012 | [NAME] Actual Pro-rata Y.O.S to be paid on 31/12/2013 | [NAME] Pro-rata Y.O.S TOTAL
1 | 100000 |  |  |  |  |  |  | 7 |  |  |  | Y | Y | 12 |  |  |  |  |  |  |  |  | 2005-05-15 00:00:00
2 | 200000 |  |  |  |  |  |  | 4 |  |  |  | N | Y | 7 |  |  |  |  |  |  |  |  | 2008-07-13 00:00:00
3 | 100000 |  |  |  |  |  |  | 3 |  |  |  | Y | Y | 4 |  |  |  |  |  |  |  |  | 2009-09-27 00:00:00
4 | 150000 |  |  |  |  |  |  | 6 |  |  |  | N | Y | 10 |  |  |  |  |  |  |  |  | 2006-09-10 00:00:00
12 | 100000 |  |  |  |  |  |  | 1 |  |  |  | N | Y | 2 |  |  |  |  |  |  |  |  | 2011-09-04 00:00:00
15 | 190000 |  |  |  |  |  |  | 2 |  |  |  | N | Y | 8 |  |  |  |  |  |  |  |  | 2010-01-10 00:00:00
16 | 100000 |  |  |  |  |  |  | 4 |  |  |  | N | Y | 10 |  |  |  |  |  |  |  |  | 2008-04-01 00:00:00
17 | 100000 |  |  |  |  |  |  | 2 |  |  |  | N | Y | 8 |  |  |  |  |  |  |  |  | 2010-06-13 00:00:00
22 | 150000 |  |  |  |  |  |  | 5 |  |  |  | N | N | 4 |  |  |  |  |  |  |  |  | 2007-06-10 00:00:00
29 | 190000 |  |  |  |  |  |  | 10 |  |  |  | N | Y | 10 |  |  |  |  |  |  |  |  | 2002-10-05 00:00:00
71 | 190000 |  |  |  |  |  |  | 8 |  |  |  | N | Y | 10 |  |  |  |  |  |  |  |  | 2004-10-13 00:00:00
72 | 150000 |  |  |  |  |  |  | 5 |  |  |  | N | Y | 9 |  |  |  |  |  |  |  |  | 2007-04-11 00:00:00
73 | 190000 |  |  |  |  |  |  | 1 |  |  |  | N | Y | 6 |  |  |  |  |  |  |  |  | 2011-06-28 00:00:00
74 | 100000 |  |  |  |  |  |  | 4 |  |  |  | N | N | 7 |  |  |  |  |  |  |  |  | 2008-03-23 00:00:00
75 | 150000 |  |  |  |  |  |  | 6 |  |  |  | N | N | 5 |  |  |  |  |  |  |  |  | 2006-01-22 00:00:00
 | Issues:
 | Col E shows complete year
 | [NAME] in Col F works perfectly
 | Col G shows year in fraction
 | Formula in Col H does not work for years in decimal
 | Column H shows a higher value than it should be, say in H2 for 6.63 years it should be AED 20,017 (see [NAME]) instead of 20,595.
 | I have used an IF formula in Col AB2 to demonstrate my requirement for fraction years
 | The formula doesn't work as after 5 years of service employee earns less no. of days and days are different for different scorings