Determine if Value is found within range of two other values

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
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

<tbody>
</tbody>

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

<tbody>
</tbody>


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?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,739
Office Version
365
Platform
Windows
Try: =IF(COUNTIFS(Sheet2!A$2:A$5,"<="&B2,Sheet2!B$2:B$5,">="&B2),"Y","N")
 

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
Thank you Stephen; I think you have the <= and >= mixed up, but this works.
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,739
Office Version
365
Platform
Windows
Great! I'm glad you could correct and make it work.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,151
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top