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.

  • 1
    Are the comments ordered by timestamps?
    – user79865
    Aug 13, 2015 at 16:45
  • @NormalHuman Google Form responses are ordered by timestamp in ascending order by default.
    – Rubén
    Aug 13, 2015 at 18:41
  • When you write "overwrite the most recent comment, in another cell?" do you mean to copy the comment into another cell or overwrite it with the content of another cell?
    – user79865
    Aug 13, 2015 at 23:22
  • Yes the comments are ordered by timestamps. The data comes from a google form.
    – steve
    Aug 14, 2015 at 13:01
  • everytime the google form is used (via a website) I want the most recent comment by that user to overwrite the last comment in a different cell (a table beside all the data). Does that make sense?
    – steve
    Aug 14, 2015 at 14:45

1 Answer 1


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:

D2 =unique(B2:B) 

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:

  • filter selects the rows where the username matches the given one;
  • vlookup find 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, vlookup will simply return the last one, i.e., the most recent comment.

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.