Return a value from a column, if date is between dates

[Flagz]

Hello there,

I have a sheet with 3 columns. A, B and C

I want a formula that I can type a start date and an end date, and it will return the Box number in Column C.
For example if I typed 10-Apr-2017 in E5 and 10-Nov-2018 in F5, starting in H5 and going down by rows
I would get 46,52,56 and 61 because those boxes contain paperwork between the dates searched.
This is what I have tried, but with no luck.

=IF(ROWS(A$2:B$150)>$G$5,"",INDEX(A$2:A$150,SMALL(IF(($A$2:$A$150>=$E$5)*($B$2:$B$150<=$F$5),ROW(A$2:$A$150)-ROW($A$2)+1),ROWS(H$5:H5))))

G5 =COUNTIFS(A2:A150,">="&E5,B2:B150,"<="&F5)

I placed the formula in H5 and hit enter. Cell is blank. Array entered, cell blank.
Not sure what is happening.
Any thoughts?
Thank you

 

[Fluff]

How about
=IFERROR(INDEX($C$2:$C$7,AGGREGATE(15,6,(ROW($C$2:$C$7)-ROW($C$2)+1)/(($E$5<=$B$2:$B$7)*($F$5>=$A$2:$A$7)),ROWS(H$5:H5))),"")

 

[jasonb75]

Your post suggests that the formula in G5 will return a count of 4. ROWS(A$2:B$150) will always be 149 regardless of what you do with it, so it will always be greater than the count and your formula will always result in a blank.

How about this one instead? Might need to array confirm.

=TEXTJOIN(", ",1,IF($A$2:$A$150<=F5,IF($B$2:$B$150>=F5,$C$2:$C$5,""),""))

 

[Flagz]

Thanks Fluff. Worked like a charm

 

[jasonb75]

Did you try the alternative that I suggested?

 

[Fluff]

Thanks Fluff. Worked like a charm

Glad we could help & thanks for the feedback.