Help with INDEX, MATCH and MAX formula - or alternative solution

Hi all,

I need some help with Excel. Ive got two worksheets in a workbook.

Sheet 1 contains a table with the following columns;
Column A : This column contains a list of unique IDs. These are 'sites'
Column B to F : Where I need some formulae.

Sheet 2 contains a table with the following columns;
Column A : This column contains a list of the unique IDs appearing in Sheet 1, Column A, BUT it can contain multiple instances of this ID (ie; the site can appear

multiple times).
Column B : This column contains a month value (the date in which the site was attended) Note that a site can have been attended in more than one month, hence the

site appearing multiple times in sheet 2.
Column T to X : These columns are columns that relate to specific tasks carried out at the site. So column T could be headed 'gardening'. The values in these columns are yes or no values.


So here is my conundrum;

I need to put a formula in Sheet 1, Column B to Column F (which correlate to the Sheet 2, Column T to X headings) where the result is;

The last month that a task was (yes, not no) carried out at a site.
So if the site appears in Sheet 2, Column A 7 times, I want the last month (Column B) where it was a 'yes' value in the relevant task columns.

Hope this makes sense.

Sample Structure - Capture.JPG
Note this is cross-posted; http://www.mrexcel.com/forum/excel-q...ml#post3828209

So, if I want to find out the last month that I last did gardening at Site 2. The formula in Sheet 1, Column B would return [Feb-14].

I've been messing about with INDEX, MATCH and MAX but to be honest I'm getting my knickers in a twist

Many thanks,
Glynn