Thread: Determine if Value is found within range of two other values Thanks: 0 Likes:  1 Post #5015309 (1)

1. Determine if Value is found within range of two other values

Hello,

[Sheet 1]
 Type Number Item Is this number within Range? Red 12354 Apple N Red 6549866 Orange N Green 13216 Apple Y Blue 894 Banana Y

[Sheet 2]
 Start End 123 129 880 990 13200 13300 8979 8980

What I am trying to do is fill out the last column on [Sheet 1]. I need to look at every row in [Sheet 1] at the [Number] field and determine whether or not that number falls between any of the ranges in [Sheet 2]. So, as you can see number 894 is found between 880 - 990, that is why we have a 'Y'; however number 12354 is not found between any of the ranges on [Sheet 2]; so the answer is 'N'. What formula do I put in D2?

2. Re: Determine if Value is found within range of two other values

Try: =IF(COUNTIFS(Sheet2!A\$2:A\$5,"<="&B2,Sheet2!B\$2:B\$5,">="&B2),"Y","N")

3. Re: Determine if Value is found within range of two other values

Thank you Stephen; I think you have the <= and >= mixed up, but this works.

4. Re: Determine if Value is found within range of two other values

Great! I'm glad you could correct and make it work.