Index & Match Formula with 2 Criteria inc. Nearest Date

[mas_uqon]

Dear All,
I have a question about index & match formula.
I want to know about the quantity based on 2 criteria.
First, one is based on OTW status and the second is based on the nearest date.
Can you all help me to complete/correct the formula I'm using?
For detail, please see attached file.
thank you.
Sample Index Match.xlsx

[menem]

I'm not sure about 'nearest' meaning.

If today is 8 Nov 2022 and the record date are
9 Nov 2022 and 7 Nov 2022 which one to be selected ?

And why not include all data in the same date?

Regards.

[mas_uqon]

'nearest' means the closest date of arrival next from today.
If today is Nov 8, then the nearest entry date is Nov 10, etc.
And about the data, this is based on the incoming schedule.
Sometimes in 1 shipment there are 1 or 2 shipments, and the shipping numbers are different from each other.
So, that's why the data is like that.
thank you.

[menem]

If you're insist not to include all of shipment in the same day.
Please try this array formula

=INDEX($B$4:$J$12,MATCH($B17,$B$4:$B$12,0),
AGGREGATE(15,6,(COLUMN($B$3:$J$3)-COLUMN($A$3))/(1/((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14)*
($B$3:$J$3=MIN(IF((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14),$B$3:$J$3))))),1))

Regards.

Note :
1 you need to unmerge part number and part name.
2 for O365 user , I think there are another better solutions.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[Glenn Kennedy]

One way (of many):

=LET(eta,$F$3:$J$3,d,SORTBY(eta,eta),r,SORTBY(F5:J5,eta),INDEX(r,MATCH(1,(d>=TODAY())*(r>0),0)))

[mas_uqon]

Wow great,
Thank you so much Menem & Glenn, that's very helpful.
But I have another question, what if I want to know about no shipping for each item.
I have tried with the same formula, but the results are not the same as the intended quantity.

[menem]

summary all shipment of the day
=SUMIFS(OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4)),$B$4:$J$4,$C$14,$B$3:$J$3,MIN(IF((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14),$B$3:$J$3)))

count shipments of the day
=COUNTIFS(OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4)),">0",$B$4:$J$4,$C$14,$B$3:$J$3,MIN(IF((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14),$B$3:$J$3)))

Regards.

[Glenn Kennedy]

or:

=LET(eta,$F$3:$J$3,d,SORTBY(eta,eta),q,F5:J5,r,SORTBY(q,eta),dv,MATCH(1,(d>=TODAY())*(r>0),0),CHOOSE({1,2},INDEX(r,dv),COUNTIFS(eta,INDEX(eta,dv),q,">0")))

[mas_uqon]

Sorry for the misunderstanding, I mean the shipping number of each item like the picture below.
Attachment 804127

Sorry because my English is not very good.

[JeteMc]

Selecting Attachment 804127 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".

[mas_uqon]

Sorry for the attachment.
please see picture below.
sample.png

[menem]

You may change the original formula from

=INDEX($B$4:$J$12,MATCH($B17,$B$4:$B$12,0),
AGGREGATE(15,6,(COLUMN($B$3:$J$3)-COLUMN($A$3))/(1/((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14)*
($B$3:$J$3=MIN(IF((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14),$B$3:$J$3))))),1))

for incoming date
=INDEX($B$3:$J$3,1,
AGGREGATE(15,6,(COLUMN($B$3:$J$3)-COLUMN($A$3))/(1/((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14)*
($B$3:$J$3=MIN(IF((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14),$B$3:$J$3))))),1))

for shipment number
=INDEX($B$2:$J$2,1,
AGGREGATE(15,6,(COLUMN($B$3:$J$3)-COLUMN($A$3))/(1/((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14)*
($B$3:$J$3=MIN(IF((OFFSET($B$4,MATCH($B17,$B$4:$B$12,0)-1,0,1,COLUMNS($B$4:$J$4))>0)*($B$4:$J$4=$C$14),$B$3:$J$3))))),1))

Regards.

[mas_uqon]

Dear menem.
it's work.
thank you so much for your help.

[Glenn Kennedy]

Since you have O365, you should take advantage of its strength and avoid volatile functions (like OFFSET) that recalculate every time anything changes. This can cause performance issues.

One formula does everything:

=LET(s,$E$2:$J$2,e,$E$3:$J$3,r,E5:J5,sS,SORTBY(s,e,1),eS,SORTBY(e,e,1),rs,SORTBY(r,e,1),d,MATCH(1,(eS>=TODAY())*(rs>0),0),nd,INDEX(eS,,d),CHOOSE({1,2,3,4,5},INDEX(rs,d),SUMIFS(r,e,nd),COUNTIFS(e,nd,r,">0"),nd,INDEX(sS,d)))

[mas_uqon]

great,
It is very helpful in solving problems in different ways.
Thank you very much, Glenn.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.