Find whether value falls within any ranges that are specified by lower and upper limits

[shimmy]

Hello, I have three columns: Column A is a lower limit, Column B is an upper limit, and Column C is my list of values. For each value in Column C, I'd like to check whether it falls within any of the ranges defined in columns A and B (so, A2-B2 is one range, A3-B3 is another range, A4-B4 is another range, etc). What is the easiest formula I can use to report whether there was a hit for my column C values?

e.g. column A: A1)1000; A2) 2000; A3) 3000

column B: B1) 1200; B2) 2300; B3) 3500

In this scenario I would check whether my values in column C fall within the ranges 1000-1200, 2000-2300, 3000-3500, etc. A value of 1100 would be a "yes" and a value of 1500 would be a no.

Thanks!

[Jonmo1]

Try

=IF(COUNTIFS(A$2:A$100,"<="&C2,B$2:B$100,">="&C2),"Yes","No")

[mcmahobt]

In the example you explained, if column A contains the min range, B contains the max range, and C contains the value to check, enter this into cell D1 and drag down:

Please Login or Register  to view this content.

This is assuming that your value can't be equal to your min/max values.

Hope this helps.

[shimmy]

Thanks for your help Jonmo1 and mcmahobt! I tried Jonmo1's solution and it works great for my needs!

[Jonmo1]

You're welcome.