Lookup the latest date associated with a project from another worksheet

amccall

In my first sheet, I have a general list of all my projects and I would like to report the latest date the project was worked on. In another worksheet, I keep a database of time/dates logged on all projects. I would like to be able to lookup the latest date a project was worked on in my first sheet. Vlookup, Match, and Index do not work. Is there anything I can use to make this work?

Frank S.

Greetings @amccall

I anticipate that the project name is used in both sheets, and you want the latest update sheet to appear on sheet 1.

For this example, sheet 1 is the location the date should appear.

Sheet 2 is where the date is entered and has the project name.

On sheet 1, for the cell, you want the date to appear. This column MUST be formatted as a date column.

Formula: =INDEX({Sheet 2 Date Column}, MATCH([Sheet 1 Project Name Column]@row, {Project Name Sheet 2}, 0))

I hope this helps.

amccall

Thanks for your response @Frank S. I tried what you are suggesting but the challenge is that the time data entered in sheet 2 has multiple entries for each project, each with a different date. Think of it as a time log database where you enter time spent on a project and it may be on different dates.

I need the latest date entered for that project. When I use match, it only looks up the first date it finds, depending on how the data is sorted. I thought there may be a way to get the MAX date but haven't been able to find a way to do that and still match the project name. Any suggestions?

Frank S.

@amcall

Can you provide a sample of the columns with different dates? We could create a helper column to look for the most current date and then use Index/match to find that date.

Example: MAX(Date Column 1@row, date column 2@row)

Use this date for your other sheet.

I hope this helps.

amccall

Time Data.png

@frank s. attached is a basic representation of the the time database. I would like to search this data for the latest date entered for a particular project name.

I am not sure how to use the helper column since I need the max of each particular project, and data entered for each project is mixed throughout the entire data. Can you help explain further how to use the helper column?