I have 3 tables that I am using and want to fill in cells in table 1 with correct values given data from that table, as well as from table 2 and table 3.
I am attaching the example of the issue.
I want to look up a value based on date, hour, and day of the week. The value is different depending on week, time of day, and day of week. I want zero if day of the week is 1 or 7. If day of week = 2-6 then I only pull values for hours 15-20 (otherwise 0). I am using an indicator value for a date range which is the first day of the date range (week). I think that is what is giving me the issues because it does not like the first or last day of a week. The middle days of the week long period lookup values correctly.
In the Example, I'm trying to fill in the column Looked Up Value. I have highlighted where it doesn't work. I think that I must be using the match and index incorrectly but can't figure out where. The general structure of the type of formula that I am using is below.
=INDEX($D$24:$K$32,MATCH($B5,$C$24:$C$32,1),MATCH($D5,$D$23:$K$23,1)*VLOOKUP($C5,$H$5:$I$11,2))
Bookmarks