Hi Gurus,
I have a sheet which has a Type of Product, Shipping Day and Order Date. Along with this there is also a matrix of dates.
Example for line 1: Formula should look for ship date in "WednesdayA" column and return the next possible shipping date (should be greater than order date).
Can you please help me with a formula using XLookup or Index/Match which can return this result. I have also added a expected outcome column in below table.
Table of Order Data
Matrix of Shipping Dates:
I have a sheet which has a Type of Product, Shipping Day and Order Date. Along with this there is also a matrix of dates.
Example for line 1: Formula should look for ship date in "WednesdayA" column and return the next possible shipping date (should be greater than order date).
Can you please help me with a formula using XLookup or Index/Match which can return this result. I have also added a expected outcome column in below table.
Table of Order Data
Group | Ship Week Day | Order Num | Order date | Ship Date | Output expected | |
A | Wednesday | 18799288 | 4/21/2022 | 5/4/2022 | ||
A | Wednesday | 18806078 | 4/21/2022 | 5/4/2022 | ||
B | Tuesday | 18812201 | 4/25/2022 | 5/10/2022 | ||
B | Wednesday | 18826555 | 4/28/2022 | 5/11/2022 | ||
B | Thursday | 18831134 | 5/2/2022 | 5/12/2022 |
Matrix of Shipping Dates:
MONDAYA | MONDAYB | TUESDAYA | TUESDAYB | WEDNESDAYA | WEDNESDAYB | THURSDAYA | THURSDAYB | FRIDAYA |
4/4/2022 | 4/11/2022 | 4/5/2022 | 4/12/2022 | 4/6/2022 | 4/13/2022 | 4/7/2022 | 4/14/2022 | 4/8/2022 |
4/18/2022 | 4/25/2022 | 4/19/2022 | 4/25/2022 | 4/20/2022 | 4/27/2022 | 4/21/2022 | 4/28/2022 | 4/22/2022 |
5/2/2022 | 5/9/2022 | 5/3/2022 | 5/10/2022 | 5/4/2022 | 5/11/2022 | 5/5/2022 | 5/12/2022 | 5/6/2022 |
5/16/2022 | 5/23/2022 | 5/17/2022 | 5/24/2022 | 5/18/2022 | 5/25/2022 | 5/19/2022 | 5/26/2022 | 5/20/2022 |
5/30/2022 | 6/6/2022 | 5/31/2022 | 6/7/2022 | 6/1/2022 | 6/8/2022 | 6/2/2022 | 6/9/2022 | 6/3/2022 |