+ Reply to Thread
Results 1 to 13 of 13

Search for Value in array given range of values in another column.

  1. #1
    Registered User
    Join Date
    05-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Search for Value in array given range of values in another column.

    A B C D
    46.14 0 46.18 46.23
    46.15 1
    46.16 0
    46.17 0
    46.18 0
    46.19 0
    46.20 0
    46.21 1
    46.22 0
    46.23 0
    46.24 1

    Using the above table, I'm looking for some formula that can tell me if "1" exists in column B, for the specified range in Columns C and D. Thus I want it to scan column B only for the rows between 46.18 and 46.23.

    This is recurring through my spreadsheet, which is much larger than what is shown. My main issue is just setting it up so it looks through column B between the values in C and D rather than just stopping at the first "1" or counting every single "1". Thanks in advance! Sorry if I did not explain it well.
    Last edited by pjw842; 05-21-2013 at 03:09 PM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for Value in array given range of values in another column.

    Ok, so what result do you expect?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    05-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Search for Value in array given range of values in another column.

    For the above example, I'd expect a "TRUE" output (I'll be using the result in an if statement). Essentially IF(["1" exists between 46.18 and 46.23],"YES","NO")

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for Value in array given range of values in another column.

    Ok, something like this...

    =IF(COUNTIFS(A1:A11,">="&C1,A1:A11,"<="&D1,B1:B11,1),"YES","NO")

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

    Re: Search for Value in array given range of values in another column.

    =SUMPRODUCT((B2:B12=1)*(A2:A12>=C2)*(A2:A12<=D2))
    If the result of this formula is >=1 then it is "yes"
    If the result is 0 thenit is "No"
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  6. #6
    Registered User
    Join Date
    05-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Search for Value in array given range of values in another column.

    That's perfect. Can't believe I didn't think to just add to COUNTIFS. Thanks so much!

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for Value in array given range of values in another column.

    You're welcome. Thanks for the feedback!

  8. #8
    Registered User
    Join Date
    05-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Search for Value in array given range of values in another column.

    Sorry, I lied. I also needed the value in col A that matches "1". I am having difficulty with my INDEX and MATCH within the range if you don't mind helping me again.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for Value in array given range of values in another column.

    Will there ever be more than one 1 within the numeric range?

  10. #10
    Registered User
    Join Date
    05-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Search for Value in array given range of values in another column.

    There are some cases that there are. In those cases, I'd just need the first value where it occurs.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for Value in array given range of values in another column.

    Try this array formula**:

    =IFERROR(INDEX(A2:A12,MATCH(1,IF(A2:A12>=C2,IF(A2:A12<=D2,B2:B12)),0)),"N/A")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  12. #12
    Registered User
    Join Date
    05-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Search for Value in array given range of values in another column.

    That worked too! So, if I have that in conjunction with another formula, it won't throw everything off if I do the array formula entry? Thanks again, you're a life saver!

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for Value in array given range of values in another column.

    It shouldn't. The only way to know for sure is to try it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1