Search for values within a range

[NeedSomeHelping]

Hi,
I have three lists of numbers; A, B and C. What I want to do is to find out if and where a number from list A is between two numbers in lists B and C. I will illustrate this below:

A.....B.....C
4.....1.....2
1.1...2....3
9.8...5....7
2.2...8....11

In this example, the number 1.1 is greater than 1 in column B and less than the corresponding 2 so this would be a positive match. 2.2 is also between 2 & 3, and 9.8 is between 8 & 11, so these would all be matches. The problem I have is being able to search through the whole list for a match. I have considered using a VB loop but would rather use functions if possible. Does anyone have any ideas? Any help would be much appreciated!

Thanks in advance
Ralph

[Dav]

What do you wish to have returned a true or a 1 if it is a match in one of the ranges? Or the row in the ranges a match occurs in?

=SUM(IF((A1>$B$1:$B$8)*(A1<$C$1:$C$8),1,0)) (entered as an array press shft ctrl enter) products 1 for a true satement

=SUM(IF((A1>$B$1:$B$8)*(A1<$C$1:$C$8),ROW($A$1:$A$8),0)) produces the row on which the match appears (entered as an array press shft ctrl enter)

I am assuming none of the ranges will overlap and have answered between technically with < > for between you may wish to use <= or >= instead

Regards

Dav

[NeedSomeHelping]

Dav you're a legend, worked a treat. My extremely untidy method involved pages and pages of AND statements VLOOKUPS the likes... thank you!

Just one more thing, although it works fine I don't fully understand how it works! Would you be able to enlighten me? Thanks again.

Ralph

[Dav]

OK the best way is often looking at the formula and slecting a bit of it and pressing F9 this will show you the values that returns.

You are familiar with the If statement if(condition,true result, false result)

well =if((a1>b1)*(a1<c1),1,0) would return a 1 if the value lies between the values of b1 and c1 otherwise it returns a 0
as a1>b1 *a1<c1 in this case equals true*true = true

We want to don this to losts of ranges, but the same logical applies to arrays. However we get lots of values as our return, in my example 8 values and we can not dispaly all 8 values, so we have to aggregare them, so they are put in sum, a max, would work equally as well and with hindsight would be safer. So if it falls in any of the ranges you get a 1, otherwise a 0. But to tell excel to think of the formula as an array you have to press Shft Ctrl Enter ans so get the funny brackets in the formula bar

=SUM(IF((A1>$B$1:$B$8)*(A1<$C$1:$C$8),1,0))

in the second formula
=SUM(IF((A1>$B$1:$B$8)*(A1<$C$1:$C$8),ROW($A$1:$A$8),0))

instread of returning a 1 in the case of it beinf tru, it returns the row number this happens on (rowa3)=3 etc

I assume you know what dollars do in formulas, stopping bits moving!

Regards

Dav