Find if Value is Between any of Timeframes

Please try =IF(E2<=$B$2,E2,IF(AND(E2>$B$2,E2<$C$2),"",IF(AND(E2>$B$3,E2<$C$3),"",IF(AND(E2>$B$4,E2<$C$4),"",E2)))) and copy down.

Please try =IF(E2<=$B$2,E2,IF(AND(E2>$B$2,E2<$C$2),"",IF(AND(E2>$B$3,E2<$C$3),"",IF(AND(E2>$B$4,E2<$C$4),"",E2)))) and copy down.

=if(e2<=$b$2,e2,if(and(e2>$b$2,e2<$c$2),"",if(and(e2>$b$3,e2<$c$3),"",if(and(e2>$b$4,e2<$c$4),"",e2))))

=if(e2<=$b$2,e2,if(and(e2>$b$2,e2<$c$2),"",if(and(e2>$b$3,e2<$c$3),"",if(and(e2>$b$4,e2<$c$4),"",e2))))

=if(e2<=$b$2,e2,if(and(e2>$b$2,e2<$c$2),"",if(and(e2>$b$3,e2<$c$3),"",if(and(e2>$b$4,e2<$c$4),"",e2))))

Hello,

Thank you for your help, but

This is example data so it contains Less rows. What is there are tens of Frames?

Is there something like CSE Formula or Recursive Lambda function?

I want formula in which I can refer to Range and that can Spill just like it does in Filter Fuction.

Thank You

It may fit you. G2=IF(SUMPRODUCT(($B$2:$B$34<E2)*($C$2:$C$34>E2))=0,E2,"") and copy down.

Hello,

Thanks for your reply.

Yes, I tried this earlier. That works. One more formula I tried is as below.

I name the range "Vals."

=FILTER(Vals,(Vals<B2)+(Vals>C2))

So it spills well.

I tried another way also like this.
=FILTER(Vals,(Vals>B2)*(Vals<C2))

This lists only values that lie in between frames.

But as soon as we refer to the range instead, that shows #N/A.

=FILTER(Vals,(Vals>B2:B4)*(Vals<C2:C4))

Is there a workaround which helps to spill?

Thanks.

It seems like prior responders gave you an answer which worked, but wasn't quite what you were looking for. Sounds like you wanted the answers in a shortened summary to avoid
having a large list that was the same size as the data sample (since the size of the results of exclusivity is significantly smaller than the data size). You refer to "spill"
which is a way that excel can provide multiple results that "spill" over the formula cell automatically. I hope I have correctly summarized your remaining goal.

I was not able to find a formula that could be entered in a single cell and spill for values that did not exist in the list. However, I was able to produce something equivalent for items
from the list which ARE within the ranges specified as follows:

=IFERROR(SMALL(IF(($E$2:$E$34>TRANSPOSE($B$2:$B$4))*($E$2:$E$34<TRANSPOSE($C$2:$C$4)),$E$2:$E$34),ROW(A1)),"")
Then you can copy down this formula until it returns "", then you have all of the values that fit within the ranges specified in a "spill" format.
The reverse is much trickier, and, while it may be possible, to me it's not worth the extra effort when a helper column would do the trick in seconds.

HTH,
Dave