Identifying most recent date and returning a value

[ROSE217]

Hi,

I have a large spreadsheet wherein I want to identify a users most recent device, to do this I need to find the most recent date and then return the value associated with that date and the user. I tried using =VLOOKUP(MAX(A2:A9),A2:C9,3,0), but it just returned the overall recent date not specific to each user. Here is an extract of the spreadsheet:

[TABLE="width: 506"]
<tbody>[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone6[/TD]
[TD="align: right"]19/01/2015[/TD]
[/TR]
[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]06/12/2012[/TD]
[/TR]
[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]28/05/2014[/TD]
[/TR]
[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone7 Plus[/TD]
[TD="align: right"]16/01/2017[/TD]
[/TR]
[TR]
[TD]00300000002bnjJAAQ[/TD]
[TD]iPhone7 Plus[/TD]
[TD="align: right"]23/05/2017[/TD]
[/TR]
[TR]
[TD]00300000002bnjJAAQ[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]24/05/2013[/TD]
[/TR]
[TR]
[TD]00300000002bnjJAAQ[/TD]
[TD]iPhone6[/TD]
[TD="align: right"]11/07/2015[/TD]
[/TR]
[TR]
[TD]00300000002cuh1AAA[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]26/08/2014[/TD]
[/TR]
[TR]
[TD]00300000002cuh1AAA[/TD]
[TD]iPhone4s[/TD]
[TD="align: right"]20/08/2014[/TD]
[/TR]
[TR]
[TD]00300000002d5NiAAI[/TD]
[TD]iPhone7 Plus[/TD]
[TD="align: right"]22/02/2017[/TD]
[/TR]
[TR]
[TD]00300000002d5NiAAI[/TD]
[TD]iPhone7 Plus[/TD]
[TD="align: right"]01/05/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
And here is an example of the output I would like to return:

[TABLE="width: 620"]
<tbody>[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone6[/TD]
[TD="align: right"]19/01/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]06/12/2012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]28/05/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300000002ayxtAAA[/TD]
[TD]iPhone7 Plus[/TD]
[TD="align: right"]16/01/2017[/TD]
[TD] iPhone7 Plus[/TD]
[/TR]
[TR]
[TD]00300000002bnjJAAQ[/TD]
[TD]iPhone7 Plus[/TD]
[TD="align: right"]23/05/2017[/TD]
[TD] iPhone7 Plus[/TD]
[/TR]
[TR]
[TD]00300000002bnjJAAQ[/TD]
[TD]iPhone5[/TD]
[TD="align: right"]24/05/2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300000002bnjJAAQ[/TD]
[TD]iPhone6[/TD]
[TD="align: right"]11/07/2015[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[CalcSux78]

You can use an array to find the max of each user code.

Excel 2010
	A	B	C	D
1	User	Device	Date
2	00300000002ayxtAAA	iPhone6	1/19/2015
3	00300000002ayxtAAA	iPhone5	12/6/2012
4	00300000002ayxtAAA	iPhone5	5/28/2014
5	00300000002ayxtAAA	iPhone7 Plus	1/16/2017	iPhone7 Plus
6	00300000002bnjJAAQ	iPhone7 Plus	5/23/2017
7	00300000002bnjJAAQ	iPhone5	5/24/2017	iPhone5
8	00300000002bnjJAAQ	iPhone6	7/11/2015
9	00300000002cuh1AAA	iPhone5	8/26/2014	iPhone5
10	00300000002cuh1AAA	iPhone4s	8/20/2014
11	00300000002d5NiAAI	iPhone7 Plus	2/22/2017
12	00300000002d5NiAAI	iPhone7 Plus	5/1/2017	iPhone7 Plus
Sheet1

Cell Formulas
Range	Formula
D2	{=IF(C2=MAX(IF(A:A=A2,C:C)),B2,"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note: my example uses the US version of the dates, but should still work for your locale.

 

[ROSE217]

Thanks, I will give that a try.