Complex Question, May be Related to VLookUp or Max? Selecting Most Recent Date/Status

Hi!

I have been trying (for the past several hours) to find a solution to an issue that I have been having to no avail.

Here is my issue:

On one sheet I have a list, let's say construction projects. Each column contains pieces of other information on each project that is formatted so I can quickly print it out for higher-ups at my job (e.g., name of builder, date of contract, etc.). I have a second sheet that has some project data that is routinely imported from an outside source. As with the first spreadsheet, each row on the imported data represents a project, but each pair of columns on this spreadsheet reflects a various stage in construction (e.g.:

Project Step1 Step1_Dt Step2 Step2_Dt Step3 Step3_Dt Step4 Step4_Dt
A Permit Requested 1/1/2012 Permit Denied 1/3/2012 Request Revised 12/4/2014
B Permit Requested 1/4/2018
C Permit Requested 2/5/2017 Permit Approved 3/1/2017 Groundbreaking 4/26/2017
D Permit Requested 7/7/2017 Permit Approved 1/17/2018
E Permit Requested 11/12/2014 Permit Approved 12/1/2014 Groundbreaking 1/2/2015 Foundation Laid 3/4/2015

As you can see, projects are in various stages at any given time. I want to add two columns to my main spreadsheet using this data to reflect 1) what stage the of development each project is in and 2) the date of the most recent stage. To get the date of the most recent stage, I tried using the following formula, but it gives me the max of all the rows, not just for the row that matches my project #: =MAX(IF(Sheet1!A2=Sheet2!A2:A6,0),Sheet2!C:C,Sheet2!E:E,Sheet2!G:G,Sheet2!I:I,0)


I tried using VLookup, but the need to specify which column to pull the information from was problematic, since the column depends on which one has the highest non-missing value. Once I get the right date, I then need to match it up with the value associated with the right/most-recent step in the process.


I'm honestly at my wits end with this. I have a feeling that there's a way to do it in excel, but this is way outside of my level of expertise. Any guidance you could provide would be greatly appreciated!


Thank you so much!

Hi, welcome to Excel Forum! A small sample workbook would really help us to solve your problem quickly and accurately.
Please remove any personal or proprietary information.
Try to use your original layout so our solutions fit your workbook.
Provide “realistic” data. Include any variations the code or formula must address.
If appropriate, simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload. Simple!

These formulas find the last instances of TEXT and NUMBER in the relevant row. They return "" if nothing is found.
In Sheet1!D2, copied down:

Please Login or Register  to view this content.

In Sheet1!E2, copied down:

Please Login or Register  to view this content.

Happy to help, thanks for the rep! If concluded, please mark your thread as SOLVED (Thread Tools up top). Regards -Lee