Doing lookups against ranges

[cadfael]

Hey all,

I've seen some solutions to similar problems but nothing seems to wor
here.

Quick background:
I'm working with a great number of records involving IP addresses i
dotted quad (1.2.3.4) and "straight decimal" formats. For those wh
don't know, you can convert from dotted quad to straight decimal b
this operation:
1. Convert each quad to hex (dec2hex(value,2)
2. Concatenate the four hex values
3. Convert the resulting hex number back to decimal

What I have are long lists of IP addresses in both formats. I want t
be able to perform automatic lookups of the elements straight decima
list against another worksheet consisting of IP address ranges: thre
columns consisting of a lower bound, upper bound, and a lookup result.
Example:

LOWER UPPER RESULT
3627848912 3627848919 America Online, Plano, TX

What I want to do is perform a function such that if a given entry i
the "straight decimal" column falls within one of those ranges, the
the result returned would be the corresponding value in the "RESULT
column.

Like I said I've seen some solutions for this but they all seem t
require contiguous ranges--nor do they work unless all possible value
for the lookup value are accounted for, whereas I want it to return
result if it finds it, but leave the field blank if I have not ye
entered the data in the ranges & results worksheet.

Any ideas?
Many thanks in advance

 

[Frank Kabel]

Hi
try something like the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100>=value)*(B1:B100<=value),0))

 

[Helen Trim]

Add a module in the Visual Basic Editor, then paste in
this function:

Function GetName(lngLookUp As Long) As String
Dim Cell As Object

GetName = ""

' Move down the list of lower limits until the lookup
value is in the range
For Each Cell In Range("Lower")
If lngLookUp >= Cell.Value And lngLookUp <= Cell.Offset
(0, 1).Value Then
GetName = Cell.Offset(0, 2).Value
End If
Next Cell

End Function

Then select the set of lower limits and make this a named
range, Lower.

Then you can use the function as normal.

HTH
Helen

 

[cadfael]

Originally posted by Frank Kabel

Hi
try something like the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100>=value)*(B1:B100<=value),0))

Hi Frank,
It works--sort of.

I attempted the technique on a simple version of my sheet, like so:

A1 = some value to be looked up

low up lookup
0 10 Network 1
11 20 Network 2
11 16 Network 3
14 16 Network 4

My function was:
MATCH(1,(A3:A6<=A1)*(B3:B6>=A1),x)
Where x was either 0 or 1.

for the experiment I first set A1 to '8'.
while x = 0 (exact match), the function returned the correct result.
However if I set x to 1, then it always returned "4" (the last row i
the list).

When I set A1 to 15 (so it would be in one of the overlapping ranges)
setting x to 0 returned the incorrect row, but setting x to 1 DI
return the correct range (e.g. in this case, row 4).

I don't know if I'm asking too much of Excel. In any case, th
technique works as you said so long as there are no overalappin
ranges. This is only a problem when you have one entity which ha
assigned to it a large IP range (like x.y.0.0-x.y.255.255, a total o
65536 total IP addresses), and it has farmed out several smaller range
to some other entities (so, entity B actually owns "x.y.1.0-x.y.1.255
and entity c owns "x.y.2.0-x.y.2.255"). Unfortunately this happen
quite a lot. It would be best if I could get a "best match, but if i
doesn't match ANYTHING then return FALSE" kind of solution.

I appreciate your assistance :

 

[Daniel.M]

Hi,

This array formula:

=INDEX($C$3:$C$6,MATCH(A1-MIN(IF(($A$3:$A$6<=A1)*($B$3:$B$6>=A1),
A1-$A$3:$A$6)),$A$3:$A$6,0))

You CAN'T use 1 as the last arg to MATCH because you also need to know if A1 is
ALSO lower than B1:B6.

You'll get #NA if your A1 number doesn't fit in ANY intervals.

Regards,

Daniel M.