H-Lookup and Dates

[JRegal]

Any help is appreciated!

I am using the following H-Lookup Formula to search for values that match with a certain date in sheet1 and move it to sheet2. Where I then shift it either left or right by C2 = one of the following #'s (-3,-2,-1,0,1,2,3,4) depending on the day I want my value to show up on.

=HLOOKUP(D4-$C$2,Sheet1!$D$5:$M$6,2,0)

My current set up looks like this:

Dates: l 9/19/14 l 9/20/14 l 9/21/14 l 9/22/14
Values: HK299

The issue I run into is when I change the days (C2) say to a plus 1 it shifts my value "HK299" from Friday (9/19/14) to Saturday (9/20/14) but I would like it to skip over Saturday and Sunday and shift under Monday (9/22/14). Also would like it skip over holidays i assign.

Any help or suggestions is appreciated.

Thanks,

 

[FormR]

Hi, try:

=HLOOKUP(WORKDAY(D4,$C$2,holiday),Sheet1!$D$5:$M$6,2,0)

Substitute "holiday" for a range of cells that contain the holiday dates you want to exclude.

 

[JRegal]

Awesome! Thanks for your help. Works great!