>> Reverse Lookup and Match days for weekly calendar

[collegeitdept]

Please help!

I need a formula that can lookup (and match the due date to the column heading weekday of top row in a worksheet calendar) a store in a data table and return it's product shipping date & time in the relevant column in a worksheet calendar.


Data Table:

In the data table, the store numbers are in column A. The product shipping days are in the first column in a 3 column group (store's may have multiple shipping days). The shipping times are in the 2nd column of the group. The shipping Due days are in the last column of the group (text is in red).


Worksheet Calendar:

This is where formulas will auto-populate the shipping days for each store. It will populate the shipping days in the proper column (Weekday) by matching it to the Due Days in the data table.

(We are only interested in due dates from December 18 to December 29)

For every weekday column (Monday - Friday) there are three columns:
- 1st Column: Shipping day
- 2nd Column: Shipping Time
- 3rd Column: Due Day (last day to ship).


Ex:

For store # 1401 - there are 3 order days for this store (between December 18 - December 29).

In the Monday column, this store does have shipping order for Wednesday 12/24 (1st column) at 4:30AM (2nd column) - because the due day is Monday (3rd column).

This 3rd column in every weekday group is what must be matched to the Due day in the data table.


Tuesday is blank because this store doesn't have a shipping order that's due on Tuesday.


In the Wednesday column, this store does have shipping order for Saturday 12/27 (1st column) at 3:30AM (2nd column) - because the due day is Wednesday (3rd column).


In the Thursday column, this store does have shipping order for Saturday 12/20 (1st column) at 3:30AM (2nd column) - because the due day is Thursday (3rd column).

For the Friday column, the store does not have an order that's due. However, this and all the other stores do not process shipping orders on the weekend (Saturday and Sunday).... so if a shipping order has a due day of Saturday or Sunday, it will be treated as a due day of Friday (and placed in the Friday column group).

I would like a formula that lookup and match the Due Day to the column heading (weekday) of a store.... (function will be probably have to be in the 3rd column (Due day) of each weekday). The first two columns (shipping day and time) will probably be use the offset formula.

The formula that I just created and sorta works is this:

=IF(WEEKDAY(VLOOKUP($A3,Sheet1!A2:O40,4,0))=$B$2,VLOOKUP($A3,Sheet1!A2:O40,4,0),IF(WEEKDAY(VLOOKUP($A3,Sheet1!A2:O40,7,0))=$B$2,VLOOKUP($A3,Sheet1!A2:O40,7,0),IF(WEEKDAY(VLOOKUP($A3,Sheet1!A2:O40,10,0))=$B$2,VLOOKUP($A3,Sheet1!A2:O40,10,0),IF(WEEKDAY(VLOOKUP($A3,Sheet1!A2:O40,13,0))=$B$2,VLOOKUP($A3,Sheet1!A2:O40,13,0),""))))

However, it is full of IF functions because I don't know a better way... But there has to be a better function.

And this doesn't work on Friday's because it doesn't include weekends.

If anyone can conjure up a better formula, please help.

I will modify the formula to for the other weekday columns.

Thanks.

 

[collegeitdept]

Again thanks for all your help.

Would you explain the 5 columns?

"Note that the number 5 in green specifies that each group contains 5 columns"

(I'm still trying to update the formula to reflect changes to cell locations).

thanks.

 

[collegeitdept]

=INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),SMALL(IF((MOD(COLUMN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2),5)+1=3)*(ISNUMBER(MATCH(WEEKDAY(INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),0),2),WEEKDAY($N$2,2)+{0,1,2},0))),COLU MN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2)+1),2))

This formula is for Friday....


Would you be able to provide the formula for Monday (regular days)? (with the added 2 sets of column groups)

Thanks.

 

[Domenic]

It looks like I misunderstood... For clarity, can you provide a small sample of data with the new layout, and describe how you'd like the calculation to take place?

 

[collegeitdept]

Sorry about that,

everything works fine with your formula....

However this new updated one is for Friday...


Last time you provided a formula for all weekdays (not Friday); and another for Friday (that also includes Saturday and Sunday).


I would like the updated formula for the regular weekdays.

Thank you so much for your help.

 

[Domenic]

...I would like the updated formula for the regular weekdays.

Here you go...

=INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),SMALL(IF((MOD(COLUMN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2),5)+1=3)*(WEEKDAY(INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),0))=WEEKDAY($B$2)),COLUMN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2)+1),2))

...confirmed with CONTROL+SHIFT+ENTER.

Thank you so much for your help.

You're very welcome!

 

[collegeitdept]

Sadly the new formula to take into account the added columns, does not work and needs a little tweeking...

Instead of looking at the last column (of the 3 column group in the data table) it returns the 1st column.

IE: for store number 208.... it should return the date Monday due date (12/22) - as it did before... now it returns "Sunday 12/21).

old formula (worked):
=INDEX(Sheet1!$C$4:$O$100,MATCH($A5,Sheet1!$A$4:$A$100,0),MATCH(TRUE,IF(MOD(COLUMN(Sheet1!$C$4:$N$100)-COLUMN(Sheet1!$C$4)+1,3)=0,WEEKDAY(INDEX(Sheet1!$C$4:$N$100,MATCH($A5,Sheet1!$A$4:$A$100,0),0))=WEEKDAY($G$3)),0))


New formula (doesn't work):
=INDEX(Sheet1!$C$4:$U$100,MATCH($A6,Sheet1!$A$4:$A$100,0),IF((MOD(COLUMN(Sheet1!$C$4:$T$100)-COLUMN(Sheet1!$C$4),5)+1=3)*(WEEKDAY(INDEX(Sheet1!$C$4:$T$100,MATCH($A6,Sheet1!$A$4:$A$100,0),0))=WEEKDAY($G$3)),COLUMN(Sheet1!$C$4:$T$100)-COLUMN(Sheet1!$C$4)+1))


I'm sorry to always bother you, it's just the new schedule is due pretty soon.

Thanks.


Also:

the small function (to get the 2nd occurrence does not work) so i had to remove it.

I'd greatly appreciate your speedy help...the due date is quickly approaching.

Thanks.

 

[collegeitdept]

Somewhat unclear... You say you've added 2 more columns for each group. So this means that each group is made up of 5 columns, correct? If so, assuming that the target/relevant columns are the 3rd column of each group, try...

=INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),SMALL(IF((MOD(COLUMN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2),5)+1=3)*(ISNUMBER(MATCH(WEEKDAY(INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),0),2),WEEKDAY($N$2,2)+{0,1,2},0))),COLU MN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2)+1),2))

...confirmed with CONTROL+SHIFT+ENTER. Note that the number 5 in green specifies that each group contains 5 columns, the number 3 in blue specifies that the target/relevant columns are the 3rd column of each group, and the number 2 in red specifies that you want the 2nd occurrence.


this could be the error..


Sorry for not clarifying this earlier.... I added 2 more groups of columns... Each column group still has 3 columns per group.


I hope this helps...


Would you kindly see if this helps with the formula? (both for regular days, and for Friday (to include Saturday and Sunday due dates)...


Also, i still would like to see if I can add the "Small" function so the first row can display the first occurrence and the 2nd to display the 2nd occurrence in the worksheet Calender.

Again thank you so much for all your help.

 

[collegeitdept]

I tweeked an earlier formula that you sent to look like this:

=INDEX(Sheet1!$C$4:$U$100,MATCH($A6,Sheet1!$A$4:$A$100,0),SMALL(IF((MOD(COLUMN(Sheet1!$C$4:$U$100)-COLUMN(Sheet1!$C$4)+1,3)=0)*(WEEKDAY(INDEX(Sheet1!$C$4:$U$100,MATCH($A6,Sheet1!$A$4:$A$100,0),0))=WEEKDAY($G$3)),COLUMN(Sheet1!$C$4:$U$100)-COLUMN(Sheet1!$C$4)+1),1))


would you look at this and see if there needs to be any changes?

I'm going to hope that this works. If you see anywhere that needs to be changed... or you notice that the formula doesn't take into account anything... please help.

Thanks.

 

[Domenic]

=INDEX(Sheet1!$C$4:$U$100,MATCH($A6,Sheet1!$A$4:$A$100,0),SMALL(IF((MOD(COLUMN(Sheet1!$C$4:$U$100)-COLUMN(Sheet1!$C$4)+1,3)=0)*(WEEKDAY(INDEX(Sheet1!$C$4:$U$100,MATCH($A6,Sheet1!$A$4:$A$100,0),0))=WEEKDAY($G$3)),COLUMN(Sheet1!$C$4:$U$100)-COLUMN(Sheet1!$C$4)+1),1))

This formula will return the first occurrence of a date, in the relevant row (store number) and columns (E, H, K, etc.), where the weekday is equal to the weekday in G3. If this is the intent, then the formula seems fine.