the challenge is: if a Holiday Date ($F) falls in between (A2:B2, A3:B3,etc) I want to copy the the Holiday Name ($G to the associated reason column $D & the Count $H to the associated $C column)
example: Vacation '17 is on 10/15/17 for 5 days because it falls on 10/3/17 & 10/16/17 the data will be copied on the $C and $D columns... how can I do that??
You can use these two array formula (click Ctrl + Shift + Enter together):
From cell C2:
=IFERROR(INDEX($H$2:$H$11,SMALL(IF((A2<=$F$2:$F$11)*(B2>=$F$2:$F$11)>0,ROW($F$2:$F$11)-ROW($F$1),""),1)),0)
From cell D2:
=IFERROR(INDEX($G$2:$G$11,SMALL(IF((A2<=$F$2:$F$11)*(B2>=$F$2:$F$11)>0,ROW($F$2:$F$11)-ROW($F$1),""),1)),"")
What these two formulas do is to find the first match and return the value.
Also, I noticed that your C9 and D12 have different results than mine. Not sure what is wrong but I am guessing it is due to the sample data you have posted here. Anyway, try and let me know.
Ctrl + Shift + Enter altogether) and you should see the curly braces will be included in the formula (see my pic). Also, those ranges need to be adjusted based on your data input. I don't recommend to use whole column since it will slow things down but you can do so if you don't have much data in there.Put this in C2 and copy across to D2, then copy down:
=IFERROR(INDEX(H$2:H$4, POWER(SQRT(MAX(IF($F$2:$F$4-$A2>=0, IF($F$2:$F$4-$B2<=0, ROW($F$2:$F$4)-ROW($F$1))))-1), 2)+1), "")
$C should have the value found in $H and $D should have the value found in $G
