Help! Complex Question - Maybe VLookUp or If/Max Commands??

[jet05c]

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.:
[TABLE="width: 719"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Step1[/TD]
[TD]Step1_Dt[/TD]
[TD]Step2[/TD]
[TD]Step2_Dt[/TD]
[TD]Step3[/TD]
[TD]Step3_Dt[/TD]
[TD]Step4[/TD]
[TD]Step4_Dt[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Permit Requested[/TD]
[TD]1/1/2012[/TD]
[TD]Permit Denied[/TD]
[TD]1/3/2012[/TD]
[TD]Request Revised[/TD]
[TD]12/4/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Permit Requested[/TD]
[TD]1/4/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Permit Requested[/TD]
[TD]2/5/2017[/TD]
[TD]Permit Approved[/TD]
[TD]3/1/2017[/TD]
[TD]Groundbreaking[/TD]
[TD]4/26/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Permit Requested[/TD]
[TD]7/7/2017[/TD]
[TD]Permit Approved[/TD]
[TD]1/17/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Permit Requested[/TD]
[TD]11/12/2014[/TD]
[TD]Permit Approved[/TD]
[TD]12/1/2014[/TD]
[TD]Groundbreaking[/TD]
[TD]1/2/2015[/TD]
[TD]Foundation Laid[/TD]
[TD]3/4/2015[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Legacy 143009]

How about using MATCH with INDIRECT?

=MAX(INDIRECT(MATCH(Sheet1!A2,$A:$A,0)&":"&MATCH(Sheet1!A2,$A:$A,0)))

 

[Legacy 143009]

and even may be

=R[1]C[MATCH(MAX(INDIRECT(MATCH(Sheet1!A2,$A:$A,0)&":"&MATCH(Sheet1!A2,$A:$A,0))),INDIRECT(MATCH(Sheet1!A2,$A:$A,0)&":"&MATCH(Sheet1!A2,$A:$A,0)),0)]

 

[Legacy 143009]

Sorry,, last edit

=INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(MAX(INDIRECT(MATCH(Sheet1!A2,$A:$A,0)&":"&MATCH(Sheet1!A2,$A:$A,0))),INDIRECT(MATCH(Sheet1!A2,$A:$A,0)&":"&MATCH(Sheet1!A2,$A:$A,0)),0),4),1,"")&1)

 

[Marcelo Branco]

Maybe...

Assumes the dates in Sheet2 are real dates (numbers), not text

Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Project​

[/TD]
[TD]

Step1​

[/TD]
[TD]

Step1_Dt​

[/TD]
[TD]

Step2​

[/TD]
[TD]

Step2_Dt​

[/TD]
[TD]

Step3​

[/TD]
[TD]

Step3_Dt​

[/TD]
[TD]

Step4​

[/TD]
[TD]

Step4_Dt​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

Permit Requested​

[/TD]
[TD]

01/01/2012​

[/TD]
[TD]

Permit Denied​

[/TD]
[TD]

01/03/2012​

[/TD]
[TD]

Request Revised​

[/TD]
[TD]

12/04/2014​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

Permit Requested​

[/TD]
[TD]

01/04/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

Permit Requested​

[/TD]
[TD]

02/05/2017​

[/TD]
[TD]

Permit Approved​

[/TD]
[TD]

03/01/2017​

[/TD]
[TD]

Groundbreaking​

[/TD]
[TD]

04/26/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

Permit Requested​

[/TD]
[TD]

07/07/2017​

[/TD]
[TD]

Permit Approved​

[/TD]
[TD]

01/17/2018​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

Permit Requested​

[/TD]
[TD]

11/12/2014​

[/TD]
[TD]

Permit Approved​

[/TD]
[TD]

12/01/2014​

[/TD]
[TD]

Groundbreaking​

[/TD]
[TD]

01/02/2015​

[/TD]
[TD]

Foundation Laid​

[/TD]
[TD]

03/04/2015​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Project​

[/TD]
[TD]

Status​

[/TD]
[TD]

Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

Request Revised​

[/TD]
[TD]

12/04/2014​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

Permit Requested​

[/TD]
[TD]

01/04/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

Groundbreaking​

[/TD]
[TD]

04/26/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

Permit Approved​

[/TD]
[TD]

01/17/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

Foundation Laid​

[/TD]
[TD]

03/04/2015​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=INDEX(Sheet2!$B:$Z,MATCH(A2,Sheet2!$A:$A,0),MATCH(9.99E+307,INDEX(Sheet2!$B:$Z,MATCH(A2,Sheet2!$A:$A,0),0))-1)

Formula in C2 copied down
=INDEX(Sheet2!$B:$Z,MATCH(A2,Sheet2!$A:$A,0),MATCH(9.99E+307,INDEX(Sheet2!$B:$Z,MATCH(A2,Sheet2!$A:$A,0),0)))

Hope this helps

M.