Google-sheets – INDEX + MATCH to return the greatest of all matching values

formulasgoogle sheetsgoogle-sheets-datesgoogle-sheets-queryvlookup

I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order.

Example: When was [Roberto]'s most recent haircut?
input:  "Roberto" 
desired output: "4/6"

Name     Date of haircut
Roberto  1/21
Xi       1/22
Jess     1/31
Roberto  4/6
Barbara  3/1
Mary     2/2
Roberto  3/10

Best Answer

=TEXT(VLOOKUP(A2, QUERY(A:B, "order by A, B desc"), 2, 0), "m/d")

Formula

=QUERY(B3:C8; "SELECT C ORDER BY B DESC LIMIT 1")

Explained

The QUERY will take both columns as dataset, but it will only show column C. Furthermore, it will sort column B descendingly (highest first) and the limit option will limit the result dataset to one row.

In this case, there's no need to calculate the highest date first.

Example

I've copied your file and added my solution: FRISBEE

Google Sheets Query – How to Reference a Cell Containing a Date

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))

Best Answer

Related Solutions

Google Sheets – Get Cell Value Based on Another Cell

Formula

Explained

Example

Google Sheets Query – How to Reference a Cell Containing a Date

Related Topic