Determine if a value falls within a horizontal and vertial range

**td87280** · 10-31-2012, 02:47 PM

What I am trying to do is determine if a value, for example 501, falls withing a a series of ranges. So there is a range 502:543 and i need to check if the value falls in that range, while also checking if the value falls between many other ranges. The second range I need to check is between 545:1000 and so on. I have attached a few examples ranges in the spreadsheet. The values in column E all need to be cheked if the fall within 502:543 and 545:1000 and so on. I have thousands of ranges and values I need to check so I can not do in manually. I tried a forumla in column F, but that does not seem to work porperly. If the value is changed to 502 then the countif function reuturns a 1, which is what I want. However, when I change the value in to 503 the formula still returns a 0, which should not be the case since 503 is within the range of 502:543. If this cannot be done with a forumla, but can be done with VBA somehow I am open to any suggestions.

p.s. I used =countif just so i could get a simple binary yes/no result. an =if could also work in theory, I just like countif for some reason.

**JBeaucaire** · 10-31-2012, 03:05 PM

Put this formula in F2 and copy down. It will tell you how many of the ranges that value falls between. Expand the ranges as needed.

=SUMPRODUCT(--($A$2:$A$15<=E2), --($B$2:$B$15>=E2))

This formula is conducting two tests and only counts a "1" when both tests equate to TRUE for a pair of values in A:B. None of the values in your sample sheet match any of the sample ranges.