Help with INDEX, MATCH and MAX formula - or alternative needed.

[glynnseal]

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 -

Note that this is cross-posted; http://www.excelforum.com/excel-for...mula-or-alternative-solution.html#post3720406

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

 

[Andrew Poulsom]

Welcome to MrExcel.

Example in B2 copied down:

=MAX(INDEX((Sheet2!A$2:A$10=A2)*(Sheet2!T$2:T$10="yes")*(Sheet2!B$2:B$10),))

 

[glynnseal]

Many thanks Andrew,

Ive tried it but I'm getting #VALUE, so I am guessing its to do with how my values are formatted. Maybe I should have said that my Month column is actually derived from a formula referring to another cell on another sheet.

Is there something missing at the end of your INDEX formula?

 

[Andrew Poulsom]

What's the formula in the Month column? It needs to return a serial date.

Nothing is missing from the end of INDEX. It is returning an array.

 

[glynnseal]

Ah okay.
The formula in the Month column is =IF('Security Contracted'!E8="","",'Security Contracted'!E8). This basically looks at another Month value in another sheet (which is formatted as mmm-yy), and if its blank, blanks it - so I don't get 0's.

 

[Andrew Poulsom]

Oh I see. The formula is failing because it's trying to multiply by "". Can you change the formula to return 0 instead of "" and format the cell as mmm-yy;; to suppress the zeroes?

 

[glynnseal]

Andrew, thanks so much for your help with this.
Its pretty much there now . Only thing I need to resolve with the last suggestion is that cells that are blank now calculate as Jan-00 rather than being blank. I guess I can conditional format that out, as its not a date that i need to be worried about.

You are a legend, many thanks Andrew.

 

[Andrew Poulsom]

If you use this number format:

mmm-yy;;

the zeroes will be suppressed. The key is the trailing second semicolon.

 

[glynnseal]

Ah, didn't spot those... but now that's worked perfectly. Brilliant, thanks Andrew!!
*buys Andrew his favourite tipple*