Google-sheets – Selecting the most recent date, where a name matches

formulasgoogle sheets

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.

Is this possible please?

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.

Best Answer

=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))
Related Topic