CODEWITHSUNDEEP
exceldateexcel-formula
CocoaNewBee
CocoaNewBee

Reputation: 83

finding a date range in an array - Excel

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??

enter image description here

Upvotes: 0

Views: 1262

Answers (3)

CocoaNewBee
CocoaNewBee

Reputation: 83

$C should have the value found in $H and $D should have the value found in $G

enter image description here

Upvotes: 0

ian0411
ian0411

Reputation: 4275

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.

Upvotes: 1

MacroMarc
MacroMarc

Reputation: 3324

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), "")

Upvotes: 0

Related Questions