VLookup formula from schedule sheet

[newbie4]

I am attaching the excel workbook here.

TeamSchedule.xlsx

I want to create a formula in the cells in column C to retrieve information from the master schedule sheet depending on the date in the "time in schedule" sheet. There are different minutes for each day of the week and Week #. Any help would be greatly appreciated.

Thank you for your assistance.

Newb

[FDibbins]

I know this can be simplified, but try this...
=INDEX('Master Schedule'!$C$3:$G$23,MATCH(TEXT(B2,"dddd")&A2,INDEX('Master Schedule'!$A$3:$A$23&'Master Schedule'!$B$3:$B$23,0),0),MATCH(WEEKNUM(B2-1)-WEEKNUM(B2-DAY(B2)-6),'Master Schedule'!$C$2:$G$2,0))

[newbie4]

Hi Ford,

Thank you for the formula, but 9/21 in cell B2 falls on the third Monday of the month, which should show up as "200" or "E3" from the master schedule sheet. The tricky part is getting the date to match where the weekday will fall in.

For example, in September - Monday will have 4 weeks, Tuesday 5 weeks, Wednesday 5 weeks, and Thursday - Sun 4 weeks. Not all months will have this arrangement.

I appreciate your help with this.

Thanks, -Newb

[newbie4]

Hello Ford,

I tweaked the formula a bit to get it working.

Please Login or Register  to view this content.

Thank you for your help,

-Newb

[FDibbins]

Awesome, happy you got it working

[newbie4]

Do you know how I can add the league name to a new formula in the Time in Schedule sheet, column A, if there is no date, but there are scheduled minutes available from the master schedule?

For example, if toddler league had minutes scheduled on the master schedule, but there is no date on the "time in schedule sheet" - how can I make the "REMAINING/BALANCE" minutes populate along with the league name. I have set up the total time box where the remaining names should go.


Thanks for your help.

newb

[newbie4]

Please see attachment.TeamSchedule.xlsx

this is the code I created, but it doesn't add up. Am I missing something?

Please Login or Register  to view this content.

I believe it is the last part of this formula to find the balance. Where do I fit in the sum of the index and match for C1:C10?

Please Login or Register  to view this content.

[newbie4]

For those of you looking for a way to calculate the sum using 2 way lookup, use this calculation.

Please Login or Register  to view this content.

Both header text in the match function must be the same.