Return the latest date based on the earliest date

[ermias]

Hi Everyone,

I have two tables one for customer shipment and the other for customer return.

The "customer shipment" table contains dates when a customer buys a reusable product and the "customer return" table contains dates that customer returns.

What i want to be able to do is determine the return date of the customer using dates "customer shipment" table.

For example if a customer took product on lets say 1/1/2021 and 1/12/2021 and in the customer return table i have this customer's return dates of

12/25/2020, 12/31/2020, 1/7/2021 and 1/20/2021.

i want to bring in the "shipment table" the return date of this customer so for the above example,

customer A took product in 1/1/2021 so in the "customer return table", search the latest date next to 1/1/2021 which would be 1/7/2021 and bring all information on that date.

Again

Customer A took product in 1/12/2021 so in the "customer return table", search the latest return date next to 1/12/2021 which would be 1/20/2021 and bring all information on that date.

I have attached a sample excel

Customer return and Shipment Matrix.xlsx

Thanks in advance for your reply

[Joske920]

Hi,

You use the new formulas

return date
=INDEX(FILTER(Table2;([@[Customer Name]]=Table2[Customer Name])*(Table2[Return Date] >= [@[Shipment Date]]));1;{1})

Doc.No
=INDEX(FILTER(Table2;([@[Customer Name]]=Table2[Customer Name])*(Table2[Return Date] >= [@[Shipment Date]]));1;{3})

But these are for the new 365 or 2019 excel version

[ermias]

Thank you for your reply but unfortunately i am only using the 2016 version of excel.

[JeteMc]

Please update the profile to indicate that the "MS-off Ver:" is 2016
The following formulas should work with the 2016 version of Excel:
1. For return date:

Please Login or Register  to view this content.

2. For doc no. of return date :

Please Login or Register  to view this content.

Let us know if you have any questions.