I am working with 3 columns, fname, lname, & date. Where there will be multiple duplicates of fname and lname. I am trying to write an INDEX & MATCH that will display the MAX result or the most current date:

(in short I have a list of individuals with the First name in Column A, Last Name in Column B, and an event for that individual in column C, each individual will have multiple events, I need to write a function that will display each individual most recent event date)

I tried with no luck, result #VALUE:


  • Do you want to get the result as shown in the following picture? imgur.com/a/Xk0UKjN Please try the MAX formula: =TEXT(MAX((Sheet1!A$2:A$9&Sheet1!B$2:B$9=E2&F2)*Sheet1!C$2:C$9),"e-m-d") – Lee Sep 10 '18 at 8:30
  • Thanks, this does returns the values I want, but since it's in a text format I am unable to sort the results by date, is there a way to have it format as a short date so I can sort it? – Jared Sep 15 '18 at 20:55
  • Yes,you can change the e-m-d to the date format you want. – Lee Sep 17 '18 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.