• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Xlookup or INDEX/ Match Help needed to calculate Date

PK_90

New Member
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
GroupShip Week DayOrder NumOrder dateShip DateOutput expected
AWednesday
18799288​
4/21/2022​
5/4/2022​
AWednesday
18806078​
4/21/2022​
5/4/2022​
BTuesday
18812201​
4/25/2022​
5/10/2022​
BWednesday
18826555​
4/28/2022​
5/11/2022​
BThursday
18831134​
5/2/2022​
5/12/2022​


Matrix of Shipping Dates:
MONDAYAMONDAYBTUESDAYATUESDAYBWEDNESDAYAWEDNESDAYBTHURSDAYATHURSDAYBFRIDAYA
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​
 

Attachments

  • Test_ship_date.xlsx
    12.2 KB · Views: 13
Without Excel for the moment so I can't give the intermediate results that allows you to follow how the formula is working.
In all honesty, the formula of Bosco is not that hard. Not basic, yet not out of this world neither.
MINIFS is much like its' cousins SUMIFS or COUNTIFS. The inner help will provide you a simple example.
The input for INDEX is a range from where you want to catch a value. These are your shopping date. Since row match is omitted and value from the column header is found, it returns a range of dates. (That's my guess. Please use the formula auditing to see how it works). I'll do that when I have the opportunity and come back on it if needed.
The same range is repeated in the last argument of MINIFS. But here the check is added so the dates are greater than the given order date.
Since dates are just numbers it makes sense to use MINIFS over XLOOKUP. The latter may not be as easy as you'd think.
 
With Excel now.
So this is where modern Excel is a bliss.
And to make the formula very explicit I also turned all ranges into tables, so one can use structured references.
This is actually how Bosco build the formula in his head so to speak.
=LET(
ShipRange,INDEX(Shippings,,MATCH(UPPER([@[Ship Week Day]])&[@Group],Shippings[#Headers],0)),
MINIFS(ShipRange,ShipRange,">"&[@[Order date]])
)

You might not have LET available, but it is used for illustration only.
 

Attachments

  • Test_ship_date.xlsx
    15.7 KB · Views: 4
I stand corrected, it's not that hard to use XLOOKUP (I forgot it can return an array :eek:)

=XLOOKUP([@[Order date]]+1,
XLOOKUP(UPPER([@[Ship Week Day]]&[@Group]),Shippings[#Headers],;Shippings);
XLOOKUP(UPPER([@[Ship Week Day]]&[@Group]),Shippings[#Headers],Shippings),,1)

Let-style:

=LET(
ShipDates,XLOOKUP(UPPER([@[Ship Week Day]]&[@Group]),Shippings[#Headers],Shippings),
XLOOKUP([@[Order date]]+1,ShipDates,ShipDates,,1)
)
 
@GraH - Guido

Could you telling me, what does the benefit in your using the LET function in compare with my old school formula in disregarding the structured references?

My old school formula :
=MINIFS(INDEX($K$2:$T$14,,MATCH(UPPER(B2)&A2,$K$1:$T$1,0)),INDEX($K$2:$T$14,,MATCH(UPPER(B2)&A2,$K$1:$T$1,0)),">"&D2)

Your Office 365 formula :
=LET(ShipRange,INDEX(Shippings,,MATCH(UPPER([@[Ship Week Day]])&[@Group],Shippings[#Headers],0)),MINIFS(ShipRange,ShipRange,">"&[@[Order date]]))

I also noted that using XLOOKUP in return 2D array is appeared longer that Index+Match. Am I right?

Best regards

bosco_yip
 
Last edited:
Hi Bosco,
I just used LET to illustrate the logic behind your formula. No need to repeat arguments as formula here. It makes the logic more visible.
Like I announced earlier, the XLOOKUP combo is not necessary better or easier. It needs 3 nested XLOOKUP! And on top you need to add 1 dat to the order date or else you get the same date as shipping date while it needs to be a later date. So in all, there is no obvious benefit of using the alternatives.
 
I have something to prove here, even if it is only that I can be more longwinded and boring than anyone else! My snappy little formula was
Code:
= LAMBDA(order,
      LET(
         group, INDEX(order,1),
         shipWkD, INDEX(order,2),
         orderDate, INDEX(order,4),
         groupDay, shipWkD & group,
         shipRange, XLOOKUP(groupDay,shippingHdr,Shippings),
         XLOOKUP(orderDate+1, shipRange,shipRange,,1)
      )
   )(orders[@])
Having put so much work into it, I decided to call it ShippingDateλ.
The date for a single row would be =ShippingDateλ(orders[@]), but rather better fun would be
Code:
= BYROW(orders, ShippingDateλ)
which returns the dates for all 10 orders.
 

Attachments

  • Test_ship_date.xlsx
    18.5 KB · Views: 3
I have something to prove here, even if it is only that I can be more longwinded and boring than anyone else! My snappy little formula was
Code:
= LAMBDA(order,
      LET(
         group, INDEX(order,1),
         shipWkD, INDEX(order,2),
         orderDate, INDEX(order,4),
         groupDay, shipWkD & group,
         shipRange, XLOOKUP(groupDay,shippingHdr,Shippings),
         XLOOKUP(orderDate+1, shipRange,shipRange,,1)
      )
   )(orders[@])
Having put so much work into it, I decided to call it ShippingDateλ.
The date for a single row would be =ShippingDateλ(orders[@]), but rather better fun would be
Code:
= BYROW(orders, ShippingDateλ)
which returns the dates for all 10 orders.
@Peter Bartholomew,

Very funny, I hope I can have a Office 365 software and join with your discussion.

Thanks for your reply

bosco_yip
 
Please try
with LAMBDA
=MAP(B2:B11&A2:A11,D2:D11,LAMBDA(a,b,LET(d,INDEX(K2:T14,,MATCH(a,K1:T1,)),MINIFS(d,d,">"&b))))

without LAMBDA
=LET(f,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,K1:T1&K2:T14)&"</m></x>","//m"),XLOOKUP(B2:B11&A2:A11&D2:D11+1,f,--RIGHT(f,5),,1))
 

Attachments

  • Test_ship_date.xlsx
    14.7 KB · Views: 6
It is very nice to see different formula ways in meet with different Excel versions from old to the newest.

Thanks to Excel Wizard who give me an idea of using Filterxml function.

Herein my contribution to give another way of using Filterxml+Textjoin formula of which can use for Excel 2019 and above.

Then,

In E2 formula copied down :

=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(K$1:T$1=UPPER(B2)&A2,K$2:T$14,""))&"</b></a>","//b[.>"&D2&"]")

Remark :

1] Excel 2019 should use "Ctrl+Shift+Enter" entry

2] Office 365 can use normal entry.

3] Adopt the Filterxml function can avoid to repeat Index+Match as posted in my previous formula Minifs+Index+Match at Level 2

79264
 

Attachments

  • Test_ship_date (BY).xlsx
    14.4 KB · Views: 4
Last edited:
O365 (insider not needed).
You may be able to dispense with the lookup table on the right (only the single value top left in cell K2 is needed(4/4/2022)) with:
Code:
=LET(a,SEQUENCE(20,,$K$2+MATCH(LEFT($B2,3),{"Mon","Tue","Wed","Thu","Fri"},0)-1+($A2="B")*7,14),MIN(IF(a>D2,a,FALSE)))
[I'm assuming the value on cell N3 is a typo? - if it's not then we can't dispense with the table. It puts the result in cell E4 into question.]
 

Attachments

  • Chandoo48109Test_ship_date.xlsx
    14.5 KB · Views: 1
O365 (insider not needed).
You may be able to dispense with the lookup table on the right (only the single value top left in cell K2 is needed(4/4/2022)) with:
Code:
=LET(a,SEQUENCE(20,,$K$2+MATCH(LEFT($B2,3),{"Mon","Tue","Wed","Thu","Fri"},0)-1+($A2="B")*7,14),MIN(IF(a>D2,a,FALSE)))
[I'm assuming the value on cell N3 is a typo? - if it's not then we can't dispense with the table. It puts the result in cell E4 into question.]
Nice. I hadn't looked at the lookup table closely enough to realise that it could be represented by a single calculation.
 
I also noted that using XLOOKUP in return 2D array is appeared longer that Index+Match. Am I right?
Yes, you are right.

Please see Level 6 reply and take note to "how do GraH - Guido using XLOOKUP in return 2D array".

Edit : I do not have Office 365, so I have no comment about how to use XLOOKUP, sorry about that.

Regards

bosco_yip
 
Last edited:
I also noted that using XLOOKUP in return 2D array is appeared longer that Index+Match. Am I right?
It may well be, after all the name XLOOKUP is longer than either INDEX or MATCH. The question then is, 'does that matter?'; is there a prize for the most concise formula? I tend to be attracted to the form that is easiest to read and to verify. Not that there is anything wrong with INDEX/XMATCH/XMATCH; in particular, I would use it if I wanted to return multiple rows and columns.

In response to an earlier comment
Could you telling me, what does the benefit in your using the LET function in compare with my old school formula in disregarding the structured references?
: the intent is to improve readability. It is often easier to work through a sequence of self-documenting steps than try to understand a nested formula as a single calculation. Occasionally there is also a computational advantage when an expression is used more than once within a formula. Unlike a defined name, a LET variable is evaluated and the value is then reused.
 
Last edited:
It may well be, after all the name XLOOKUP is longer than either INDEX or MATCH. The question then is, 'does that matter?'; is there a prize for the most concise formula? I tend to be attracted to the form that is easiest to read and to verify. Not that there is anything wrong with INDEX/XMATCH/XMATCH; in particular, I would use it if I wanted to return multiple rows and columns.

In response to an earlier comment : the intent is to improve readability. It is often easier to work through a sequence of self-documenting steps than try to understand a nested formula as a single calculation. Occasionally there is also a computational advantage when an expression is used more than once within a formula. Unlike a defined name, a LET variable is evaluated and the value is then reused.
@Peter Bartholomew and @GraH - Guido,

Thank you both of you to explain my question regarding Let and Index+Match function

This is my understanding point after reading both of your few posts how to handle Let function

1] I noted that in Single Conditional Lookup, Index+Match is shorter and better than Let function.

2] But in Multiple Conditional Lookup, Let function will show its benefit in a simpler approach in compare with Index+Match function

Best regards

bosco_yip
 
@Peter Bartholomew and @GraH - Guido,
1] I noted that in Single Conditional Lookup, Index+Match is shorter and better than Let function.
2] But in Multiple Conditional Lookup, Let function will show its benefit in a simpler approach in compare with Index+Match function
Agreed. After all, the use of LET itself increases the level of nesting and hence complexity. To pay off, it has to make the formula it implements more readable, more computationally efficient or easier to edit and maintain. The use of any named LAMBDA function also has its trade offs. It hides detail, which one may think of as being bad. On the other hand, where the code involves arcane techniques (FILTERXML being a case in point) it is often easier to verify that the output conforms to the intent of the function than it is to check the formula detail.
 
Back
Top