VLOOKUP in a range, possible?

  • Thread starter Thread starter RedAlert80
  • Start date Start date
R

RedAlert80

I have a worksheet with 1 column with some ip numbers in it. This
worksheet has multiple rows.

In a second worksheet I have 3 columns, column 1 name of the person,
column 2 begin of a ip range, column 3 end of ip range.
This worksheet has multiple rows.

With which function can I look if the value of an ip is in the range of
that person, then it returns the name of the person?

I made it work with looking for an exact ip adress but can't get it
working with a range. I used vlookup. I thought something like between,
but cant find that. There is also not an option to use <> in the
vlookup function.

Anyone knows a solution in excel?
 
I am sure it can be done with worksheet functions but it will be horrible,
so I propose a UDF solution.

Add this code to a normal VBA code module

Function IPInRange(source As String, IPStart As Range, IPEnd As Range) As
Long
Dim aIP, astartIP, aendIP
Dim cell As Range
Dim sourceIP As Double
Dim startIP As Double
Dim endIP As Double

Application.Volatile
IPInRange = 0
aIP = Split(source, ".")
sourceIP = ConvertIP(source)
For Each cell In IPStart
startIP = ConvertIP(cell.Value)
endIP = ConvertIP(IPEnd.Cells(cell.Row, 1).Value)
If sourceIP >= startIP And sourceIP <= endIP Then
IPInRange = cell.Row
End If
Next

End Function

Private Function ConvertIP(IP As String) As Double
Dim cVal As Double
Dim iNumPos As Long
Dim iDotPos As Long

cVal = 0
iNumPos = 1
iDotPos = InStr(iNumPos, IP, ".")
Do While iDotPos > iNumPos
cVal = cVal * 256 + CDbl(Mid(IP, iNumPos, iDotPos - iNumPos))
iNumPos = iDotPos + 1 'move onto to next separator
iDotPos = InStr(iNumPos, IP, ".")
Loop
ConvertIP = cVal * 256 + CDbl(Mid(IP, iNumPos))

End Function


The function IPInRange will return the index of the IP address within the
lookup tgable, 0 if not found. Such as

=InRange(A1,Sheet2!$B$1:$B$2,Sheet2!$C$1:$C$2

and you can then get the name using INDEDX, such as

=INDEX(Sheet2!$A$1:$A$2,IPInRange(A1,Sheet2!$B$1:$B$2,Sheet2!$C$1:$C$2),1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I am sure it can be done with worksheet functions but it will be horrible,
so I propose a UDF solution.
Click to expand...

They'd be long, but horrible?
Add this code to a normal VBA code module

Function IPInRange(source As String, IPStart As Range, IPEnd As Range) As Long
Click to expand...
...

Quibble: 'Match' or 'Find' rather than 'In' would be more descriptive that you
were returning an index rather than a boolean.

More seriously: the ideal way to handle using a table containing IP range end
points (the OP's second table) would be to ensure that the IPs in that table
were formatted with leading zeros in each octet. So 001.023.123.000 rather than
1.23.123.0. Then lookups would reduce to the relatively trivial problem of
formatting the lookup value IP with leading-zero octets.
 
trying to return an index for a match between a value and several
ranges. format would likely be something like:

Lower Limit (>) Upper Limit (<=)
- 21
21 64
64 155
155 299


I altered earlier code to come up with the following, which does not
work:

Function InRange(Source As String, ValStart As Range, ValEnd As Range)
As Long
Dim Cell As Range

Application.Volatile
For Each Cell In ValStart
If Source > ValStart And Source <= ValEnd Then
InRange = Row
End If
Next

End Function
 
Harlan Grove said:
...

They'd be long, but horrible?
Click to expand...

Probably not horrible, but a UDF seems better (IMO)
Long
..

Quibble: 'Match' or 'Find' rather than 'In' would be more descriptive that you
were returning an index rather than a boolean.
Click to expand...

Agreed it is a quibble, but it is better as well. Originally my routine was
constructed to return a boolean, but I changed in. to an index, and
obviously never even considered the function name.
More seriously: the ideal way to handle using a table containing IP range end
points (the OP's second table) would be to ensure that the IPs in that table
were formatted with leading zeros in each octet. So 001.023.123.000 rather than
1.23.123.0. Then lookups would reduce to the relatively trivial problem of
formatting the lookup value IP with leading-zero octets.
Click to expand...

I like this solution of converting the IP address to a full numeric value.
Interestingly, I forgot a credit in my original post. I got this code from
one Harlan Grove <G> many moo ns ago and incorporated into an IP address
sort addin I created . I adapted this solution from my addin (and have since
added it to my addin). It works in this problem, but I take the lookup point
as well.

Regards

Bob
 
It's hard for me to understand the VB script. Also other people have to
work with this sheet, so i don't wana make it difficult.

Even if it is horrible it is not really a mather because the ip range
list is very small. I have 17 IP Ranges.

Name ; IP Begin ; IP End

This in 17 rows

Then i have one row in anthor worksheet with ip numbers, that list is
long.

I want something like:

IF{ IP >= IpBegin AND IP <= IpEnd } Then IpName Else N/A.

To use this for al the 17 ranges i can use something like IfElse.

But that is not possible in Excel. Using these programming code.
 
so one way to do it is to have in your case there seperate columns tha
perform >,<= calcs. Use each to perfrom a third of the calcs. then us
a fourth column to choose a result from one of them

so so you have ranges 1-17, each has one unit (1-2,2-3). label the hig
value in each range with the label HIRANGE#. do this in a separat
table.

in the first column, use this:
=IF(A2<=HIRANGE6,IF(A2<=HIRANGE1,1,IF(A2<=HIRANGE2,2,IF(A2<=HIRANGE3,3,IF(A2<=HIRANGE4,4,IF(A2<=HIRANGE5,5,IF(A2<=HIRANGE6,6,"")))))),"")

in the second, use this:
=IF(A2>HIRANGE6,IF(A2<=HIRANGE11,IF(A2<=HIRANGE7,7,IF(A2<=HIRANGE8,8,IF(A2<=HIRANGE9,9,IF(A2<=HIRANGE10,10,IF(A2<=HIRANGE11,11,"")))))),"")

in the third, use this:
=IF(A2>HIRANGE11,IF(A2<=HIRANGE12,12,IF(A2<=HIRANGE13,13,IF(A2<=HIRANGE14,14,IF(A2<=HIRANGE15,15,IF(A2<=HIRANGE16,16,IF(A2<=HIRANGE17,17,"")))))),"")

finally, in the fourth column, use this:
=IF(B2="",IF(C2="",D2,C2),B2
 
Back
Top