Writing a conditional formula to Search with number ranges

[Guest]

I'm trying to write a conditional formula that will indicate if the data in a
indiviual cell in column A falls within the range of the data in Columns B
and C. See example data below. There are roughly twenty thousand lines
that I need to search if a particular number falls within the range. Is
there a formula that will capture range of data???



A B C

491987312 491987100 491987199
491987200 491987299
491987300 491987399
491987400 491987499

I've also listed the columns of range numbers on the same sheet as my data.
Could this be on a different sheet? Could the program be written using data
from seperate sheets?

 

[Guest]

If you want

A B C D

491987312 491987100 491987199 False

Then it's a simple formula:

=IF(AND((A1>=B1),(A1<=C1)), TRUE, FALSE)

If you want D1 to tell what row it falls into (e.g. D1 would equal "3") then
you'd be better off writing a custom function like the one below. With this
function you would enter into cell D1 the following formula:

=WhereAmI(A1, C1:C4)

And it would return the value 3.

Function WhereAmI(FindMe As Range, InHere As Range) As Integer

Dim myrow As Integer
'Assuming the FindMe value will
'fall into the first row.
myrow = 1
For Each cell In InHere
If FindMe.Value > cell.Value Then
'Our search value is greater
'than the max for this row.
myrow = myrow + 1
End If
Next cell

WHEREAMI = myrow

End Function

Hope this helps.

Bob Tulk
MOUS(XP/97)