Testing if value exists in multiple ranges

[Ratguy]

Hello all,

I've got a list of data in the following format:

Name Date StartMetrage EndMetrage

The start and end metrages are locations along a railway line where we have surveyed the track centreline. I would like to be able to look up a specific metrage and test whether it falls within any of the metrage ranges in the data set above. For example, say I want to look up metrage 1.245 and see whether it falls within ranges 2.5-6.4, and 0.2-1.8. In this case, the answer would be 'true'.

Note that in the list of ranges there are several which overlap. I'm just looking to test if a value shows up in any of the ranges, so multiple positive results should just return 'true'.

I've been trying Array functions and Vlookup but I'm not good with either of these so have yet to be successful. Is there a better way?

Thanks!

 

[lrobbo314]

Let's say that you type the value that you are looking up into cell K1. Then, you can create a column to be used as a filter. So, if your data starts in A1, The formula you would put into E2 would be... =AND($K$1>=C2,$K$1<=D2). This will give you Trues and Falses and you can then filter your results on that column for True values.

 

[Ratguy]

Let's say that you type the value that you are looking up into cell K1. Then, you can create a column to be used as a filter. So, if your data starts in A1, The formula you would put into E2 would be... =AND($K$1>=C2,$K$1<=D2). This will give you Trues and Falses and you can then filter your results on that column for True values.

That would be the brute force way of doing it. Trouble is, I have many values that need to be looked up. And my list of metrages is also quite long. I was hoping to find an easier way to do this.

 

[Marcelo Branco]

Ratguy

I think COUNTIFS does what you want, but we need a small data sample (~10 rows) along with expected results for testing purposes.
To post a data sample see section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Anyway, see if this example helps


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Metrage​

[/td][td]

Result​

[/td][td][/td][td]

Range​

[/td][td]

Lower​

[/td][td]

Higher​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1,245​

[/td][td]

TRUE​

[/td][td][/td][td]

1​

[/td][td]

0,2​

[/td][td]

1,8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2,843​

[/td][td]

TRUE​

[/td][td][/td][td]

2​

[/td][td]

1,6​

[/td][td]

3,2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

6,526​

[/td][td]

FALSE​

[/td][td][/td][td]

3​

[/td][td]

2,5​

[/td][td]

6,4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td]

4​

[/td][td]

3,6​

[/td][td]

5,8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td]

5​

[/td][td]

6,7​

[/td][td]

8,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B2 copied down
=COUNTIFS(E$2:E$6,"<="&A2,F$2:F$6,">="&A2)>0

Hope this helps

M.

 

[Ratguy]

Ratguy

I think COUNTIFS does what you want,

This is EXACTLY what I wanted. I was trying Countif in one of my functions but couldn't get that to work. I didn't know about COUNTIFS. Thanks so much!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.