Setting up a calendar with matching ID and dates

Try:

=IFERROR(LOOKUP(2,1/(Sheet1!$G$2:$G$1000=$A2)/(Sheet1!$L$2:$L$1000=B$1),Sheet1!$E2:$E$1000),"")

It works on the first cell but doesn't on the other cells...please see the screenshot.
Thanks.

T2.PNG

Try this

Remember to ctrl shift enter after inserting the formula as this is array.

=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$B$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")

Nice..this works,thanks ....but when I copy the formula to other cells it doesn't work

=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$B$1,Sheet1!$H$2:$H$1000&Sheet1!$L$2:$L$1000,0)),"")
(the only change made is bold above)

=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$D$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")
this works...though I made some changes (in bold)

=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$F$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")
doesn't work


The cells where your formula works....I see { } , apparently they are not in the cells where the formula is not working, could
be because of this?

tr.PNG

hmm, could be your the issue of the name A1 , A2 , A3 etc as they are merging cell. can you attach a sample.

oh just a guess, when you edited the bold part, did you ctrl shift enter?

yup...I did ctrl shift enter in all

I changed names from A1, A2 ...to XL12, XL13.....

sample sheet attached herewith, please check.

thanks.

Got it working....selected cell and tapped F2, then ctrl shift enter
Thank you so much for your help

you are welcome =)

a request if this is possible to do .....I would like to fill up the name in the cells of start date, not sure if I am able to explain well...please see the screenshot, thanks.

example
start date 1/1/16
end date 4/1/16....
so I would like the name in cells C2:C4 (1/1/16 to 3/1/16)


d1.PNG

Hmm....let me think ....thank you once again for your help. Highly appreciate.
Have a great weekend.

That's correct....start date is in column C and end date in column B.
I copy pasted your formula ....it works but get numbers instead of name, please see the
attached screenshot.

Format of all cells are General

S1.PNG

You need to format the cell to custom date which is same as column A.

Got it....
what about the dates that show up? can't those be hidden?

s2.PNG

I have changed the thread title, hope that's okay, if not, please tell me what
should I rename it to...thank you.

Hope the title is okay now, if not please suggest me what should I rename it to...thank you for your help.

Hi mikehk,

It should be fine now. Have you tested my above method?

This formula nearly works....I copy pasted in cell E2 as you said....but the first name now show up as a date
Please see the screenshot


=IF(AND(IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A3&'2016'!D$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")="",D3="",MAX(INDEX((Sheet1!$L$2:$L$1000=D$1)*Sheet1!$H$2:$H$1000,0))>=$A3,AGGREGATE(15,6,Sheet1!$G$2:$G$1000/(Sheet1!$L$2:$L$1000=D$1),1)<=$A3),$A3,IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A3&'2016'!D$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),""))


a1.PNG

My bad. The formula i provided for you is for cell E3,

try this again at cell E2

Please Login or Register  to view this content.

CTRL SHIFT ENTER

please see the screenshot, the dates in between show up

s4.PNG

It shows blank cells in the beginning, which is exactly how it should be ,
hope same can be done with dates in between, please see the screenshot.
thanks.

S5.PNG

yea I noticed that as well. This is really tough. I tried a few ways but to no avail. Even adding helper column I also can't get it to work the way you wanted. Hopefully others can help you out. =(

No worries....thank you soooo much, highly appreciate your efforts.
Another alternative to make it work is by apply conditional formatting to fill color
in those cells.
I will open a separate thread for that.

Thank you once again.
Have a great weekend.

Best regards

No worries....thank you soooo much, highly appreciate your efforts.
Another alternative to make it work is by apply conditional formatting to fill color
in those cells.
I will open a separate thread for that.

Thank you once again.
Have a great weekend.

Best regards