1

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.

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

1 Answer 1

1
=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))
4
  • 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
  • 1
    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
    – user0
    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))
    – user0
    Sep 4, 2018 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.