In Google Sheets I have three columns: Timestamp, Username and Comment. How can I search a column for the most recent comment of a username and overwrite the most recent comment, in another cell? There will be up to 8 usernames and comments input from a Google Form.
So, given a three-column table
Timestamp | Username | Comment, you want a separate table that shows the most recent comment by every user:
Username | Most Recent Comment. Let's say the first one is in columns A:C, and the other table will be D-E.
I would do the following:
which picks the usernames from column B, listing each once. Then,
E2 =vlookup(999999, filter(A$2:C, B$2:B=D2), 3)
This does two things:
filterselects the rows where the username matches the given one;
vlookupfind the last row where the value in column A is <=999999. Since "999999" corresponds to November 25 of year 4637, it's safe to say that all of your timestamps are below this value. Thus,
vlookupwill simply return the last one, i.e., the most recent comment.