I have been scratching my head on this one for a few hours now.
I have a spreadsheet with 3 columns. Column A contains the Date Period formatted as such 200401 for the first month of 2004. Column B contains a Reference Number.
In Column C I would like to have a formula that returns the highest value in Column A using the reference number from Column B. As shown below
COL A COL B COL C
200407 DIFA0694 200408
200408 DIFA0694 200408
200311 DIFA0704 200801
200403 DIFA0704 200801
200801 DIFA0704 200801
200311 DIFA0712 ......
200311 DIFA0712 ......
200409 DIFA0712 ......
200411 DIFA0712 ......
200312 DIFA0736 ......
200312 DIFA0736 ......
200512 DIFA0736 ......
200404 DIFA0763 ......
200405 DIFA0763 ......
200405 DIFA0763 ......
200807 DIFA0763 ......
200405 DIFA0780 ......
200408 DIFA0780 ......
200312 DIFA0780 ......
200401 DIFA0780 ......
So, the COL C value should be the most recent period for the job reference shown in COL B.
I have tried this using a combination of MAX, INDEX and MATCH but to no avail. I believe this is because the INDEX, MATCH does not return an array of values.
I would appreciate any guidance that anybody could offer
