Results 1 to 4 of 4

Thread: Determine if Value is found within range of two other values

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Location
    Ohio
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Aug 2015
    Location
    Ohio
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •