# Search for a number in a range within another range of numbers

#### cpike

##### New Member
Hi,

I have two sets of numerical data that I am trying to match if a alphanumeric criteria is met.

Both ranges are numeric (from and to depth measurements)
The first range is continuous, From_L will always equal the previous To_L

The second range (From_S & To_S) may not be continuous but there will be no overlapping values.

I need to flag any To_S value that falls within a From_L - To_L range that has the Type LO

I've noted the From_S - To_S range that meets the criteria in the table - marked with FAIL
Note that the two ranges are variable, the range values will rarely match and they won't often be in the same row.

 From_L To_L Type From_S To_S 0 10.5 QQ 8.0 9.0 OK 10.5 11.5 LO 9.0 10.2 OK 11.5 25.6 QN 10.2 10.9 FAIL 25.6 55.1 VV

<tbody>
</tbody>

Any help would be greatly appreciated!!

Thanks
Christian

Last edited:

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Why are you lining up the red "10.2 10.9 FAIL" data with the red "10.5 11.5 LO" and not "0 10.5 QQ"
The "S" data being checked overlaps two lines of "L" data

Why are you lining up the red "10.2 10.9 FAIL" data with the red "10.5 11.5 LO" and not "0 10.5 QQ"
The "S" data being checked overlaps two lines of "L" data

I'm only looking for any To_S that falls within a From_L and To_L range with the Type LO

So in the example I need to flag any To_S that is between >10.5 and <=11.5

I'm only looking for any To_S that falls within a From_L and To_L range with the Type LO

So in the example I need to flag any To_S that is between >10.5 and <=11.5

Why do you have OK for the other To_S entries? These are not associated with Type LO...

Why do you have OK for the other To_S entries? These are not associated with Type LO...

I just put the OK there to indicate that those ranges do not match the LO range.
In the task that I am doing those ranges are valid.

I need to prevent users from adding any number range that is within a Type LO range.
I don't want users to create From_S - To_S ranges that fall within a range that contains the Type LO

One set of number ranges is created first (shown as First Ranges below).

First Ranges
 From_L To_L Type 0 10.5 QQ 10.5 11.5 LO 11.5 25.6 QN

<tbody>
</tbody>

Then users create another range.
I need to flag any To_S value that falls within any range from the First Ranges (above) that has the type LO

Second Ranges

 From_S To_S Valid/Not Valid 8.0 9.0 VALID 9.0 10.2 VALID 10.2 10.9 NOT VALID

<tbody>
</tbody>

I hope this makes more sense

Last edited:
Let A1:C4 house 'First Ranges' and F1:H4 'Second Ranges', with headers in the first row.

In H2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=IF(ISNUMBER(MATCH(1,IF(\$C\$2:\$C\$4="LO",IF(G2>=\$A\$2:\$A\$4,
IF(G2<=\$B\$2:\$B\$4,1))),0)),"NOT VALID","VALID")
``````

Let A1:C4 house 'First Ranges' and F1:H4 'Second Ranges', with headers in the first row.

In H2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=IF(ISNUMBER(MATCH(1,IF(\$C\$2:\$C\$4="LO",IF(G2>=\$A\$2:\$A\$4,
IF(G2<=\$B\$2:\$B\$4,1))),0)),"NOT VALID","VALID")
``````

I've done a bit of testing with it and it seems to work nicely, very much appreciated!

I've done a bit of testing with it and it seems to work nicely, very much appreciated!

You are welcome.

If "From S" is in E1, perhaps this in G2

=IF(0=SUMPRODUCT(--((\$A\$2:\$A\$5<=E2:F2)*(E2:F2<=\$B\$2:\$B\$5))*(\$C\$2:\$C\$5="LO")),"OK","fail")

1,196,102
Messages
6,013,476
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back