>> 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.

 

[Domenic]

The following formula should be equivalent to your formula...

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

...confirmed with CONTROL+SHIFT+ENTER. Adjust the range, accordingly. However, in your example, B2 contains the day of the week (Monday). Does B2 contain a true date value formatted to display the day of the week or is the value in B2 a text value. If the former, then I think you'll need to replace...

=$B$2

with

=WEEDAY($B$2)

If the latter, then I think you'll need to replace...

WEEKDAY(INDEX(Sheet1!$B$2:$O$40,MATCH($A3,Sheet1!$A$2:$A$40,0),0))=WEEKDAY($B$2)

with

TEXT(INDEX(Sheet1!$B$2:$O$40,MATCH($A3,Sheet1!$A$2:$A$40,0),0),"dddd")=$B$2

Hope this helps!

 

[collegeitdept]

Now I'm sorta lost... it works for the Monday column... How do I "adjust" it for the other day columns, like Tuesday and Wednesday?

 

[collegeitdept]

I found it.

It works, thanks!

However, how do I make it work for Friday (so it includes Saturday and Sunday)?

 

[collegeitdept]

I'm also using the "=WEEDAY($B$2)" in the formula because it's using the true date format to only display the weekday.

 

[Domenic]

For Friday, try...

N3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

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

...where N2 contains the date, formatted to display the weekday.

Hope this helps!

 

[collegeitdept]

Domenic,

I sincerely appreciate all your help.

I have one slight adjustment that maybe you could help me with.

The Worksheet Calendar currently only supports showing one week at a time. I realized that in the data table, there some store's have information for delivery due dates for more than one week. (ie: a store may have information on 2 Monday's).


I have just changed the Worksheet Calender so that it's double spaced. (Every store has 2 rows). The first row will have information for the first listed week, the 2nd row will be for the 2nd week (ie: 2nd Monday due date that's listed in the data table).

How do I adjust the formula in the 2nd row to grab information on the 2nd weekday (2nd Monday due date; or 2nd Tuesday due date; etc...)

Thanks.

 

[Domenic]

Try the following, which need to be confirmed with CONTROL+SHIFT+ENTER...

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

For Friday, try...

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

Note that the number 2, in red, specifies that you want the 2nd occurrence.

Hope this helps!

 

[collegeitdept]

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


Thanks for the formula...

I do have another question... I received more data for the data table about shipping days and due days.

I would like to add 2 more column groups (3 columns per group)... so now the last column is T.

Do you know how to modify this formula to reflect the added columns... (I know adding columns means that I have to update the formula's in the entire calendar).


F.Y.I. Also.... some cells (store's have fewer shipping and due days) contain blanks. But I think this formula can handle that without throwing off an error.

 

[Domenic]

Thanks for the formula...

You're very welcome!

I do have another question... I received more data for the data table about shipping days and due days.

I would like to add 2 more column groups (3 columns per group)... so now the last column is T.

Do you know how to modify this formula to reflect the added columns... (I know adding columns means that I have to update the formula's in the entire calendar).

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))),COLUMN(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.

F.Y.I. Also.... some cells (store's have fewer shipping and due days) contain blanks. But I think this formula can handle that without throwing off an error.

I'm assuming that when you say blanks you mean empty cells, not formula blanks (""), correct? If so, the only formula that will need to be modified is the one for Friday, since an empty cell will be evaluated as Saturday and Friday's calculation includes Saturday...

=INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),SMALL(IF(ISNUMBER(INDEX(Sheet1!$B$2:$T$40,MATCH($A3,Sheet1!$A$2:$A$40,0),0))*(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))),COLUMN(Sheet1!$B$2:$T$40)-COLUMN(Sheet1!$B$2)+1),2))

Hope this helps!