Determine if Value is found within range of two other values

[kparadise]

Hello,

[Sheet 1]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Number
[/TD]
[TD]Item
[/TD]
[TD]Is this number within Range?
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]12354
[/TD]
[TD]Apple
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]6549866
[/TD]
[TD]Orange
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]13216
[/TD]
[TD]Apple
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[TD]894
[/TD]
[TD]Banana
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]

[Sheet 2]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Start
[/TD]
[TD]End
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]129
[/TD]
[/TR]
[TR]
[TD]880
[/TD]
[TD]990
[/TD]
[/TR]
[TR]
[TD]13200
[/TD]
[TD]13300
[/TD]
[/TR]
[TR]
[TD]8979
[/TD]
[TD]8980
[/TD]
[/TR]
</tbody>[/TABLE]


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?

 

[StephenCrump]

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

 

[kparadise]

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

 

[StephenCrump]

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