Excel Lookup IP addresses in multiple ranges

D

Drew123

New Member
Joined
Jan 22, 2015
Messages
5
I am trying to find a formula for column A that will check an IP address in column B and find if if falls into a range (or between) 2 addresses in in two other columns. C and D.
E.G.
Valid?addressrange
startend
yes10.1.1.510.1.1.010.1.1.31
yes10.1.3.1310.1.2.1610.1.2.31
no10.1.2.710.1.1.12810.1.1.223
no10.1.1.6210.1.3.010.1.3.127
yes10.1.1.910.1.4.010.1.4.255
no10.1.1.50……
yes10.1.1.200……

<tbody>
</tbody>

Sorry about the formatting..

I can do a lateral check with
=IF(AND((B3>C3),(B3 < D3)),"yes","no")
which only checks 1 address against the range next to it. I need something that will check the 1 ip address against all of the ranges. i.e. rows 2 to 100..
This is checking access list rules against routes to see if i can eliminate redundant rules... but has other uses if i can get it going. To make it extra special i can not use VBA macros to get it done.
I'm thinking some kind of index match to look it up in an array but not sure how to apply it. I don't know if it can even be done. Good luck.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
To do this without vba I believe that you would have to 'fill out' all your IP addresses. That is, instead of 10.1.1.5 you would need to make it 010.001.001.005 otherwise your > type comparisons will not be reliable. For example:

<b>IP</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:90px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">FALSE</td><td style="font-size:10pt; ">10.1.1.5</td><td style="font-size:10pt; ">10.1.1.13</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A16</td><td >=B16<C16</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; "></span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> </a>

Now 010.001.001.005 is less than 010.001.001.013 but the formula fails because of the lack of consistent formatting.

So, the lookup table in particular would need to be 'filled out' and then sorted in ascending order of the 'start' values.
Is it feasible for you to get the table expanded in that way, or should we look at doing all this by formula too?

If you did allow vba then a user-defined function could do this fairly easily as follows:

Rich (BB code): 
Function CheckIP(sIP As String, rTbl As Range) As String
  Dim a As Variant, bits As Variant, e As Variant
  Dim i As Long, lStart As Long, lEnd As Long
  
  bits = Split(sIP, ".")
  sIP = ExpandIP(sIP)
  a = rTbl.Value
  CheckIP = "No"
  For i = 1 To UBound(a)
    If sIP >= ExpandIP(a(i, 1)) Then
      If sIP <= ExpandIP(a(i, 2)) Then
        CheckIP = "Yes"
        Exit For
      End If
    End If
  Next i
End Function

Function ExpandIP(IPstr As Variant) As String
  Dim bits As Variant, e As Variant
  
  bits = Split(IPstr, ".")
  For Each e In bits
    ExpandIP = ExpandIP & Format(e, "|000")
  Next e
End Function

.. and used in the sheet like this, copied down.


Excel 2010 32 bit
ABCD
1Valid?addressrange
2startend
3Yes10.1.1.510.1.1.010.1.1.31
4Yes10.1.3.1310.1.2.1610.1.2.31
5No10.1.2.710.1.1.12810.1.1.223
6No10.1.1.6210.1.3.010.1.3.127
7Yes10.1.1.910.1.4.010.1.4.255
8No10.1.1.50
9Yes10.1.1.200
IP
Cell Formulas
RangeFormula
A3=CheckIP(B3,C$3:D$7)
 
Upvote 0
You are correct, my lateral check fails in some situations..
The addresses I used are an example only. I am having to pull the IP address to check and the ranges from 2 different columns of text strings. Putting them in individual octets would probably be less work to get done.
For the most part the ranges only change in the 4th octet of the IP address.
I',m using a company PC and for most users macros are disabled, so sharing these files would not be possible.
Also the IP addresses are not just in the 10.0.0.0 range.
 
Upvote 0
There may be a better way, but the only way coming to me so far if we exclude vba is quite long-winded and assumes that you don't have any values in the "start" column that are the same as each other.
Even then, I'm not absolutely certain that this won't fail in some circumstance.

In the example below:
- Original data in columns B:D
- Formula in F3 is copied across to G3 and down to the bottom of the original lookup table in C:D
- Formula in I3 is copied down to the last row of the original lookup values in column B
- Formula in J3 is an array formula. It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across to K3 & down to the bottom of the original lookup table in C:D.
- Formula in A3 is copied down to the last row of the original lookup values in column B


Excel 2010 32 bit
ABCDEFGHIJK
1Valid?addressrange
2startendExpanded StartExpanded EndExpanded lookup valueSorted StartMatching End
3Yes10.1.1.510.1.1.010.1.1.31010.001.001.000010.001.001.031010.001.001.005010.001.001.000010.001.001.031
4Yes10.1.3.1310.1.2.1610.1.2.31010.001.002.016010.001.002.031010.001.003.013010.001.001.128010.001.001.223
5No10.1.2.710.1.1.12810.1.1.223010.001.001.128010.001.001.223010.001.002.007010.001.002.016010.001.002.031
6No10.1.1.6210.1.3.010.1.3.127010.001.003.000010.001.003.127010.001.001.062010.001.003.000010.001.003.127
7Yes10.1.1.910.1.4.010.1.4.255010.001.004.000010.001.004.255010.001.001.009010.001.004.000010.001.004.255
8No10.1.1.50010.001.001.050
9Yes10.1.1.200010.001.001.200
10
IP (2)
Cell Formulas
RangeFormula
F3=TEXT(LEFT(C3,FIND(".",C3)),"000.")&TEXT(MID(SUBSTITUTE(C3,".",REPT(" ",20)),20,20),"000.")&TEXT(MID(SUBSTITUTE(C3,".",REPT(" ",20)),40,20),"000.")&TEXT(MID(SUBSTITUTE(C3,".",REPT(" ",20)),60,20),"000")
I3=TEXT(LEFT(B3,FIND(".",B3)),"000.")&TEXT(MID(SUBSTITUTE(B3,".",REPT(" ",20)),20,20),"000.")&TEXT(MID(SUBSTITUTE(B3,".",REPT(" ",20)),40,20),"000.")&TEXT(MID(SUBSTITUTE(B3,".",REPT(" ",20)),60,20),"000")
A3=IF(I3<=VLOOKUP(I3,J$3:K$7,2,1),"Yes","No")
J3{=INDEX(F$3:F$7,MATCH(ROWS($J$2:J2),COUNTIF($F$3:$F$7,"<="&$F$3:$F$7),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Peter_SSs said:
Even then, I'm not absolutely certain that this won't fail in some circumstance.
Click to expand...
.. and here's an example of it failing, though I don't know if such a circumstance is possible with your data. This fails because ..
a) The second start/end range lies entirely within the first. That is, 10.1.1.128 is larger than 10.1.1.0 and 10.1.1.223 is smaller than 10.1.1.301, and
b) the lookup value is larger than the second 'end' value but less than or equal to the first 'end' value.

The result should be 'Yes' because the lookup value lies in the first range. :(


Excel 2010 32 bit
ABCDEFGHIJK
1Valid?addressrange
2startendExpanded StartExpanded EndExpanded lookup valueSorted StartMatching End
3No10.1.1.30010.1.1.010.1.1.310010.001.001.000010.001.001.310010.001.001.300010.001.001.000010.001.001.310
410.1.1.12810.1.1.223010.001.001.128010.001.001.223010.001.001.128010.001.001.223
IP (3)
Cell Formulas
RangeFormula
F3=TEXT(LEFT(C3,FIND(".",C3)),"000.")&TEXT(MID(SUBSTITUTE(C3,".",REPT(" ",20)),20,20),"000.")&TEXT(MID(SUBSTITUTE(C3,".",REPT(" ",20)),40,20),"000.")&TEXT(MID(SUBSTITUTE(C3,".",REPT(" ",20)),60,20),"000")
I3=TEXT(LEFT(B3,FIND(".",B3)),"000.")&TEXT(MID(SUBSTITUTE(B3,".",REPT(" ",20)),20,20),"000.")&TEXT(MID(SUBSTITUTE(B3,".",REPT(" ",20)),40,20),"000.")&TEXT(MID(SUBSTITUTE(B3,".",REPT(" ",20)),60,20),"000")
A3=IF(I3<=VLOOKUP(I3,J$3:K$4,2,1),"Yes","No")
J3{=INDEX(F$3:F$6,MATCH(ROWS($J$2:J2),COUNTIF($F$3:$F$6,"<="&$F$3:$F$6),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks for your assistance Peter. There is a limit for using the array function. I should have explained that my address ranges for checking are 20k long. it is a large routing table!, and the array (J3) uses too much resource. I should have mentioned ( didn't realise) I have a similar thread in stack overflow and have an answer that looks ok.

arrays - Excel Lookup IP addresses in multiple ranges - Stack Overflow

I am still working through it. Because i have found larger address ranges which change the 2nd and 3rd octet of the address space for some ranges and i have had to create a lookup table to get the end address. The VBA config you have provided might be the best solution but that does limit the use in my case.

Thanks again for your efforts.
Regards.
Drew
 
Upvote 0
Hello Peter,

Your post looks very useful and like something I need - I have a smaller report from AntiVirus software that I want to match to sites by IP range. The idea to make IP unified in xxx.xxx.xxx.xxx is great, I just can't make it work :(

The formula is not working in Excel2013. I tried to change comma with semicolоn but no luck.

I am doing something wrong:

=TEXT(LEFT(D2;FIND(".";D2));"000.")&TEXT(MID(SUBSTITUTE(D2;".";REPT(" ";20));20;20);"000.")&TEXT(MID(SUBSTITUTE(D2;".";REPT(" ";20));40;20);"000.")&TEXT(MID(SUBSTITUTE(D2;".";REPT(" ";20));60;20);"000")

Any answer will be very usefull.
 
Upvote 0
miroslav82 said:
The idea to make IP unified in xxx.xxx.xxx.xxx is great, I just can't make it work :(

The formula is not working in Excel2013. I tried to change comma with semicolоn but no luck.

I am doing something wrong:

=TEXT(LEFT(D2;FIND(".";D2));"000.")&TEXT(MID(SUBSTITUTE(D2;".";REPT(" ";20));20;20);"000.")&TEXT(MID(SUBSTITUTE(D2;".";REPT(" ";20));40;20);"000.")&TEXT(MID(SUBSTITUTE(D2;".";REPT(" ";20));60;20);"000")
Click to expand...
Welcome to the MrExcel board!

Whilst I am not using Excel 2013, there is nothing in the formula that should not work in 2013.
If your version of Excel requires semicolon instead of comma as a separator then it looks like you have converted it correctly.

Clearly, you can see from the earlier posts that the formula works for me.

When you say it doesn't work, what does it do?
- return an error message?
- return the an incorrect result?
- return nothing?
- does it work for some values and not for some values?
- something else?

My first guess would be to look for invisible leading or trailing characters.
Try entering the formula =LEN(D2) Does that return the expected result from manually counting the characters in D2?
If you use =LEN(D2)-LEN(SUBSTITUTE(D2,".","")) does it return 3? (Don't forget to change to semicolon again if required)

Does your version of Excel use "." as the thousands separator? If so, I guess that could be another possible problem area.

If still unsure, give us some sample data and tell us what results the formula above returns.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Excel IP Address Parse and Search
2 3
Replies
20
Views
7K
birdman91
B

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top