Lookup Data table with multiple dates

[MsAccountant]

I am trying to create a lookup on multiple data fields that have multiple dates based on statuses. I have created a concatenate to link the specific fields that I am trying to isolate, but cannot execute the lookup that would yield the last date that work was completed.

The example is as follows:

Concatenate

Manifest ID

Box ID

File Name

Client Pickup

Client Pickup Location

Status

I have multiple statuses that the project can pass through. I would need to populate the status and the as of date. It could have 5 statuses, but the status with the current date is what I want to capture.

I created a concatenate to link the two data sources together, but I am having difficulty creating a formula what would look at the most current date and yielding that status. Can you help?

 

[Joe4]

Welcome to the Board!

I think it would be extremely beneficial to us if you could provide some sample data and expected output (just be sure to remove or "dummy up" any sensitive data first).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MsAccountant]

CONCATENATE

Manifest ID

Box ID

File Name

Client Pickup

Client Pickup Location

In Secured Storage

In Processing

Scanned

In Management Review

Quality Control

Picked Up For Return

File Deletion Date

Return Milestone Date

2JP01File 1

2​

JP01

File 1

4/16/2024​

Jackson, FL

4/16/2024​

4/20/2024​

4/21/2024​

4/22/2024​

4/23/2024​

5/5/2024​

7/20/2024​

5/20/2024​

2JP01File 2

2​

JP01

File 2

4/16/2024​

Jackson, FL

4/16/2024​

4/20/2024​

4/21/2024​

4/22/2024​

4/23/2024​

5/5/2024​

7/20/2024​

5/20/2024​

2JP01File 3

2​

JP01

File 3

4/16/2024​

Jackson, FL

4/16/2024​

4/20/2024​

4/21/2024​

4/22/2024​

4/23/2024​

5/5/2024​

7/20/2024​

5/20/2024​

2JP01File 4

2​

JP01

File 4

4/16/2024​

Jackson, FL

4/16/2024​

4/20/2024​

4/22/2024​

4/22/2024​

4/23/2024​

5/5/2024​

7/20/2024​

5/20/2024​

4JP02File 1

4​

JP02

File 1

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

4/21/2024​

4/22/2024​

5/20/2024​

4JP02File 2

4​

JP02

File 2

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

4JP02File 3

4​

JP02

File 3

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

4JP02File 4

4​

JP02

File 4

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

7JP03File 1

7​

JP03

File 1

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

4/21/2024​

4/22/2024​

5/20/2024​

7JP03File 2

7​

JP03

File 2

4/16/2024​

Atlanta GA

4/16/2024​

4/21/2024​

7JP03File 3

7​

JP03

File 3

4/16/2024​

Atlanta GA

4/16/2024​

4/21/2024​

7JP03File 4

7​

JP03

File 4

4/16/2024​

Atlanta GA

4/16/2024​

4/21/2024​

7JP04File 1

7​

JP04

File 1

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

4/21/2024​

4/22/2024​

4/23/2024​

5/20/2024​

7JP04File 2

7​

JP04

File 2

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

7JP04File 3

7​

JP04

File 3

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

7JP04File 4

7​

JP04

File 4

4/16/2024​

Atlanta GA

4/16/2024​

4/20/2024​

10JP05File 1

10​

JP05

File 1

4/16/2024​

Cleveland OH

4/16/2024​

10JP05File 2

10​

JP05

File 2

4/16/2024​

Cleveland OH

4/16/2024​

10JP05File 3

10​

JP05

File 3

4/16/2024​

Cleveland OH

4/16/2024​

10JP05File 4

10​

JP05

File 4

4/16/2024​

Cleveland OH

4/16/2024​

April Report Would look at only April Dates and prior if still not worked on. The last data point within the row

May Report Would look at only April Dates and prior if still not worked on. The last data point within the row

and so on

 

[Joe4]

OK, so that is the data you are starting with.
Based on that data, what would the expected result look like?