donjon
Reputation: 21
Excel - check if a value is within a number of ranges
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.
Upvotes: 2
Views: 9609
Answers (2)
BigBen
Reputation: 49998
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")
Upvotes: 1
Harun24hr
Reputation: 36780
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")))
Upvotes: 0
Related Questions
- VBA to check if multiple values fall within multiple ranges
- Excel Formula - If any cells within a range contain specific value, then
- Testing Where Any Of A Multiple Of Values Appears In A Range
- vba code that is in several cells and checks several ranges
- How to get a specific output if a value is within a range
- Excel: Check to see if Two Values are Between Two Other Values
- Checking if a Value is between a set of range intervals
- Check if a value in a range for a specific key is less than / more than a number
- Excel | See if a Number in One Column is in within a Number Range in Another Column
- Excel to calculate if values in ranges meet certain criteria using VBA