# 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
 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​

#### Attachments

• 12.2 KB Views: 12

#### bosco_yip

##### Excel Ninja
Try,

In G2, formula copied down :

=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)

#### PK_90

##### New Member
Awesome @bosco_yip . Appreciate the quick response. That worked perfectly. Although, would this be possible using XLOOKUP or an nested IF? I'd appreciate if there's something easier that a beginner could understand.

#### GraH - Guido

##### Well-Known Member
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.

#### GraH - Guido

##### Well-Known Member
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(
MINIFS(ShipRange,ShipRange,">"&[@[Order date]])
)

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

#### Attachments

• 15.7 KB Views: 3

#### GraH - Guido

##### Well-Known Member
I stand corrected, it's not that hard to use XLOOKUP (I forgot it can return an array )

=XLOOKUP([@[Order date]]+1,

Let-style:

=LET(
XLOOKUP([@[Order date]]+1,ShipDates,ShipDates,,1)
)

#### bosco_yip

##### Excel Ninja
@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)

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:

#### GraH - Guido

##### Well-Known Member
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.

#### GraH - Guido

##### Well-Known Member
Also, for me personally LET is 'easy' because I'm such a PQ adept.

#### Peter Bartholomew

##### Well-Known Member
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

• 18.5 KB Views: 1

#### bosco_yip

##### Excel Ninja
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.

bosco_yip

#### Excel Wizard

##### Active Member
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

• 14.7 KB Views: 4

#### bosco_yip

##### Excel Ninja
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

#### Attachments

• 14.4 KB Views: 3
Last edited:

#### p45cal

##### Well-Known Member
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

• 14.5 KB Views: 0

#### Peter Bartholomew

##### Well-Known Member
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.

#### maafkari4

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

#### bosco_yip

##### Excel Ninja
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:

#### Peter Bartholomew

##### Well-Known Member
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:

#### bosco_yip

##### Excel Ninja
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

##### Well-Known Member
@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.