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

1. ## 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.

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

Ok, so what result do you expect?

3. ## 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. ## 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. ## 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"

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. ## Re: Search for Value in array given range of values in another column.

You're welcome. Thanks for the feedback!

8. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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!

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

#### 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