I'd like a formula to find the highest date based on 2 columns: Column A is shipping date; Column B is delivery date; Column C is where I want the results to be.
The logic is: What is the last day I can place an order this month and still receive the shipment before the last day of this month. The results is based on the entire columns. The results could be on another cell, not necessarily on results column.
Here's the an example table:
| Shipping Date | Delivery Date | Results |
|---|---|---|
| 01/24/2023 | 01/28/2023 | 01/26/2023 |
| 01/25/2023 | 01/30/2023 | 01/26/2023 |
| 01/26/2023 | 01/31/2023 | 01/26/2023 |
| 01/27/2023 | 02/01/2023 | 01/26/2023 |
Hope someone could help me!
CodePudding user response：
In E2, use the following formula:
=LET(delivery, B2:B5, shipping, A2:A5,
BYROW(shipping, LAMBDA(a, MAX(FILTER(shipping, MONTH(delivery) = MONTH(a)))))
)
Here is the corresponding output:
Basically by row we filter the shipping dates to consider only the delivery is during the same month, and from there we take the maximum date.
Note: You can't use MAXIFS(max_range, criteria_range1, criteria1) to replace MAX(FILTER()), because the second input argument must be a range, and we have an array after applying the MONTH function.