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?  Reply With Quote

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")  Reply With Quote

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.  Reply With Quote

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

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

User Tag List

determine, found, number, ranges, [sheet 