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:

00300000002ayxtAAA	iPhone6	19/01/2015
00300000002ayxtAAA	iPhone5	06/12/2012
00300000002ayxtAAA	iPhone5	28/05/2014
00300000002ayxtAAA	iPhone7 Plus	16/01/2017
00300000002bnjJAAQ	iPhone7 Plus	23/05/2017
00300000002bnjJAAQ	iPhone5	24/05/2013
00300000002bnjJAAQ	iPhone6	11/07/2015
00300000002cuh1AAA	iPhone5	26/08/2014
00300000002cuh1AAA	iPhone4s	20/08/2014
00300000002d5NiAAI	iPhone7 Plus	22/02/2017
00300000002d5NiAAI	iPhone7 Plus	01/05/2017

<tbody>
</tbody>

And here is an example of the output I would like to return:

00300000002ayxtAAA	iPhone6	19/01/2015
00300000002ayxtAAA	iPhone5	06/12/2012
00300000002ayxtAAA	iPhone5	28/05/2014
00300000002ayxtAAA	iPhone7 Plus	16/01/2017	iPhone7 Plus
00300000002bnjJAAQ	iPhone7 Plus	23/05/2017	iPhone7 Plus
00300000002bnjJAAQ	iPhone5	24/05/2013
00300000002bnjJAAQ	iPhone6	11/07/2015

<tbody>
</tbody>

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.