Pull Most Recent Date in data set with recurrent entries

  • Hello all,

    Attached is a sample file.


    Sheet1 contains a table named 'VisitLog' which will be updated as needed, and there will be multiple entries for a given manager. For example, I met with John in September of 2018 and March of 2017.

    Sheet2 is more of a report that will show other relevant information, along with the most recent visit. My goal is for cells in column C to show the most recent contact with a given manager.

    I've tried =MAX(VLOOKUP(B4&"*",VisitLog,3,TRUE))

    The formula will produce an output, but it is not the most recent visit.

    Please help with a formula that will live in Sheet2, but reference table "VisitLog" in Sheet1, and pull the most recent date for a given manager.

    Also, is there a way to perform a VLookUp using table headers instead of column numbers? I've seen a number of websites that try to address this but I've always felt like the community at ozgrid is hands down smarter than everywhere else, so you guys might have a more elegant solution. The reason I ask about this is my manager who is NOT excel savvy at all tends to make a lot of changes to spreadsheets (she breaks them constantly) and referencing a header name would preserve the functionality of the formula when she wantonly adds columns.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!