You can do this using conditional formatting...

highlight your table of data (Sheet1!A2:B11) Go to conditional formatting and choose "Custom formula" option.  Then, the formula you'll use is:

=OR($A2<=$B2+7,$A2>=$B2-7)

the format will test every cell in A against every cell in B within your range. The Or allows for the value in B to be above or below A up to 7 days.

Let me know if you want to see it in action (access needed) or if you have any questions!

Eric

This sounds like a question on analysis or data visualization (and not sheets functionality per se). I took a shot at trying to show the data in a visual that i think would be a clear representation of what you're looking to show. 

Here's a sample chart:

Feel free to take a look at the document i worked in here and let me know if you have any questions.

Based on your feedback to other experts, it looks like you want to know how to do the matching using INDEX(...MATCH...) for a given cell. 

In the EM tab, i did this for you in cell B3

=index(db!$B$2:$Q$4,match($A3,db!$A$2:$A,0),MATCH(B$2,db!$B$1:$Q$1,0))

you are putting the entire db array into the first input of index correctly, but then for match, you're using syntax that looks like it fits better with SUMPRODUCT (i'm happy to elaborate if needed). index has two additional inputs after the array to index: row and column. so we're matching on row by finding what element "A3" is in the array's first column, and matching on column by finding what column B2 is in the arrays first row. 

Hope this helps - please let me know!

Eric

The above solution will work for what you're trying to do.
the "and(H2>0,...)" included in the above solution is just an added condition to restrict formatting to cases where H2>0. it's not necessary to do what you're asking.

we don't have edit access, but this should do what you're looking for:

=byrow(C4:Z23,LAMBDA(x,sumifs(x,C$3:Z$3,"Hdr2")))

enter that formula in cell B4 and it should populate all of column B from 4 down.

EM Schedule and EM Example show what i mean.  This works, but i dont see how this scales very nicely - what does your full calendar look like?

Eric

Best,
Eric

Also, because your query only spits out 7 columns, the blank row you pull in should also only be 7 columns. Doesn’t matter which since they’ll be blank, so you can replace:

{BCH!H2:O2} with BCH!A2:G2 and it should work. 

try that?

=TEXTJOIN(" ",TRUE, TO_DATE(VLOOKUP(indirect("MasterDoc!"&address(Column(O2)-13,Row(O2)+13)),MasterDoc!D113:E133,1,0)),VLOOKUP(indirect("MasterDoc!"&address(Column(O2)-13,Row(O2)+13)),MasterDoc!D113:E133,2,0))

This is obviously looking up O2. and if you drag this right, it'll reference O3, O4, etc.

if you drag down, P2, Q2 etc.

another way to do this inside the indirect is to use the "address" function.

Address takes in two inputs for location and then another for what should lock (where the "$"s go)

so it's address([row number], [column number], [lock preference]). 

To replace O1 in your function, you would therefor want it to say:

I subtracted 14 from the first input because Column number of O1 is 15 but i need the first input to be a 1 for Row 1. I added 14 to the second input because row number for O1 is 1 but i need the second input to be 15 for the Column to spit out an O. This address will spit out $O$1.

Then when you drag this over to P1, column of P1 is 16 and so 16-14=2 (for row 2) and row of P1 is 1 so 1+14=15 for column O) so the output becomes $O$2.

Note: the $'s in the output won't matter since the inputs are lacking them, as you fill, it'll change dynamically in the manner you desire based on your question.

IMPORTANT: Depending on where this sits (which function), you may need to put this inside INDIRECT(...). Once you share, we can better advise.