Excel - check if a value is within a number of ranges-CodePudding

I'm a noob. I would like to make Excel find out whether a value is within any of a set of specified ranges. See enclosed picture for my best effort this far. Doesn't work, unfortunately.

CodePudding user response：

Use COUNTIFS:

=IF(COUNTIFS(D:D;"<="&A2;E:E;">="&A2);"Yes";"No")

If your version of Excel supports Dynamic Arrays:

=IF(COUNTIFS(D:D,"<="&A2:A9,E:E,">="&A2:A9),"Yes","No")

CodePudding user response：

If anyone interested to make it dynamic (spill array approach) then could go with BYROW().

=BYROW(A2:A8,LAMBDA(x,IF(MAX(($D$2:$D$8<=x)*($E$2:$E$8>=x))=1,"Yes","No")))