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

[cpike]

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

 

[konew1]

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

 

[cpike]

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

 

[Aladin Akyurek]

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

 

[cpike]

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

Hi Aladin,
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

 

[cpike]

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

 

[Aladin Akyurek]

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:

=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")

 

[cpike]

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:

=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")

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

 

[Aladin Akyurek]

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

You are welcome.

 

[mikerickson]

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")