# 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.  Register To Reply

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

Ok, so what result do you expect?  Register To Reply

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")  Register To Reply

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")  Register To Reply

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"  Register To Reply

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!  Register To Reply

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

You're welcome. Thanks for the feedback!   Register To Reply

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.  Register To Reply

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?  Register To Reply

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.  Register To Reply

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.  Register To Reply

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!  Register To Reply

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!   Register To Reply