Between Range of Cells

[ItalianPlatinum]

Hello,
I have a VERY large file that I in summary need to find a number between a range of cells. So Column A I have a low number Column B I have a high number (goes to row 1,000) and need to know if this other number (column C) falls between ANY of those ranges. See below for a small snipbit.

[TABLE="width: 411"]
<tbody>[TR]
[TD]Combined Low[/TD]
[TD]Combined High[/TD]
[TD]Number[/TD]
[TD]Between any Ranges?[/TD]
[/TR]
[TR]
[TD]30010000[/TD]
[TD]30010059[/TD]
[TD]30010000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33395000[/TD]
[TD]33395030[/TD]
[TD="align: right"]30010001[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33000100[/TD]
[TD]33000149[/TD]
[TD="align: right"]19000000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33690000[/TD]
[TD]33690000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33355000[/TD]
[TD]33355049[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32300000[/TD]
[TD]32300232[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32400131[/TD]
[TD]32400849[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32300401[/TD]
[TD]32300449[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33343000[/TD]
[TD]33343000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[aaronbens]

What is the expected output? Are you looking for "Yes" as in it does fit within one of the ranges? Are you looking for it to return the row number of the range it is in? Are there multiple ranges that it could be in and what would you want to see if it were in 2 different ranges?

 

[Chris Macro]

If your data starts in cell A2, this formula should work (place in cell D2): =SUMPRODUCT(--(C2>=$A$2:$A$10),--(C2<=$B$2:$B$10))

The formula will return a 0 if the number does not fall between any of the ranges.

Hope that helps!

 

[ItalianPlatinum]

What is the expected output? Are you looking for "Yes" as in it does fit within one of the ranges? Are you looking for it to return the row number of the range it is in? Are there multiple ranges that it could be in and what would you want to see if it were in 2 different ranges?

Yes or No; 1 or 0. Any distinction really

 

[ItalianPlatinum]

If your data starts in cell A2, this formula should work (place in cell D2): =SUMPRODUCT(--(C2>=$A$2:$A$10),--(C2<=$B$2:$B$10))

The formula will return a 0 if the number does not fall between any of the ranges.

Hope that helps!

NICE! Thank you. This worked perfectly. Just had to wrestle with the formatting they all had to be in text. Minor stuff though. Have a nice weekend Chris.

 

[Chris Macro]

NICE! Thank you. This worked perfectly. Just had to wrestle with the formatting they all had to be in text. Minor stuff though. Have a nice weekend Chris.

Glad I could help! Cheers!