# Selecting the most recent date, where a name matches?

I'd like to search a spreadsheet for a name, and pick out the most recent date next to it if possible please? My data looks a little like:

``````|      Name      |     Date         |
|----------------|------------------|
|      Sally     |     04/06/2017   |
|----------------|------------------|
|      Tim       |     12/04/2018   |
|----------------|------------------|
|      Sally     |     16/09/2019   |
|----------------|------------------|
|      Sally     |     29/02/2018   |
``````

In this case, I'd like to return `12/04/2018` for Tim, and `16/09/2019` for Sally.

Update: still struggling with this formula:

``````=ARRAYFORMULA(SORT(VLOOKUP(QUERY(
{ROW(Events!A2:A), SORT(Events
!A2:D)},
"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''", 0),
{ROW(Events!A2:A), SORT(Events!A2:D)},
{2, 3},
0), 1, 0))
``````

The dates are `Events!A` and the names are `Events!D` for what it's worth.

• FYI `29/02/2018` is not a valid date Sep 2, 2018 at 23:33

``````=ARRAYFORMULA(SORT(VLOOKUP(QUERY(
{ROW(B2:B), SORT(A2:B)},
"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''", 0),
{ROW(B2:B), SORT(A2:B)},
{2, 3},
0), 1, 0))
``````

this kind of creates a new virtual array in a virtual column which sorts that column with dates and with the rest of the data feeds `QUERY` which is returned in `VLOOKUP` consisting of columns `2` and `3` (virtual column 1 is omitted) and lastly output gets sorted by column `1` and `0` indicates that sorting is descending (1 would be ascending)

european syntax:

``````=ARRAYFORMULA(SORT(VLOOKUP(QUERY(
{ROW(B2:B) \ SORT(A2:B)};
"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''"; 0);
{ROW(B2:B) \ SORT(A2:B)};
{2 \ 3};
0); 1; 0))
``````
• Thank you so much! This looks perfect, but I'm struggling to get it working. Specifically the formula outputs N/A, and I'm noticing that it keeps changing `SORT(Events!C2:A)` to `SORT(Events!A2:C)` for no reason, even if I clear the formula and start again :(
– Nick
Sep 3, 2018 at 12:21
• A2:C is valid syntax not C2:A. other than that I cant help you further unless you provide a copy of your sheet or at least mention which columns you had dedicated for this Sep 3, 2018 at 22:41
• Thank you so much, I've updated the original post - the date column is Events!A and the names column is Events!D. I'm looking up the vlookup query function right now but I'm struggling to get to grips with it, sorry :(
– Nick
Sep 4, 2018 at 21:39
• @Nick try: `=ARRAYFORMULA(SORT(VLOOKUP(QUERY( {ROW(Events!D2:D), SORT(Events!A2:D)}, "select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''", 0), {ROW(Events!D2:D), SORT(Events!A2:D)}, {2, 5}, 0), 1, 0))` Sep 4, 2018 at 22:10