Excel Lookup IP addresses in multiple ranges

[Drew123]

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.
[TABLE="width: 328"]
<tbody>[TR]
[TD]Valid?[/TD]
[TD]address[/TD]
[TD="colspan: 2"]range[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]start[/TD]
[TD]end[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD]10.1.1.5[/TD]
[TD]10.1.1.0[/TD]
[TD]10.1.1.31[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD]10.1.3.13[/TD]
[TD]10.1.2.16[/TD]
[TD]10.1.2.31[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD]10.1.2.7[/TD]
[TD]10.1.1.128[/TD]
[TD]10.1.1.223[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD]10.1.1.62[/TD]
[TD]10.1.3.0[/TD]
[TD]10.1.3.127[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD]10.1.1.9[/TD]
[TD]10.1.4.0[/TD]
[TD]10.1.4.255[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD]10.1.1.50[/TD]
[TD]…[/TD]
[TD]…[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD]10.1.1.200[/TD]
[TD]…[/TD]
[TD]…[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Peter_SSs]

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:

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
	A	B	C	D
1	Valid?	address	range
2			start	end
3	Yes	10.1.1.5	10.1.1.0	10.1.1.31
4	Yes	10.1.3.13	10.1.2.16	10.1.2.31
5	No	10.1.2.7	10.1.1.128	10.1.1.223
6	No	10.1.1.62	10.1.3.0	10.1.3.127
7	Yes	10.1.1.9	10.1.4.0	10.1.4.255
8	No	10.1.1.50
9	Yes	10.1.1.200
IP

Cell Formulas
Range	Formula
A3	=CheckIP(B3,C$3:D$7)

 

[Drew123]

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.

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Valid?	address	range
2			start	end		Expanded Start	Expanded End		Expanded lookup value	Sorted Start	Matching End
3	Yes	10.1.1.5	10.1.1.0	10.1.1.31		010.001.001.000	010.001.001.031		010.001.001.005	010.001.001.000	010.001.001.031
4	Yes	10.1.3.13	10.1.2.16	10.1.2.31		010.001.002.016	010.001.002.031		010.001.003.013	010.001.001.128	010.001.001.223
5	No	10.1.2.7	10.1.1.128	10.1.1.223		010.001.001.128	010.001.001.223		010.001.002.007	010.001.002.016	010.001.002.031
6	No	10.1.1.62	10.1.3.0	10.1.3.127		010.001.003.000	010.001.003.127		010.001.001.062	010.001.003.000	010.001.003.127
7	Yes	10.1.1.9	10.1.4.0	10.1.4.255		010.001.004.000	010.001.004.255		010.001.001.009	010.001.004.000	010.001.004.255
8	No	10.1.1.50							010.001.001.050
9	Yes	10.1.1.200							010.001.001.200
10
IP (2)

Cell Formulas
Range	Formula
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.

 

[Peter_SSs]

Even then, I'm not absolutely certain that this won't fail in some circumstance.

.. 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
	A	B	C	D	E	F	G	H	I	J	K
1	Valid?	address	range
2			start	end		Expanded Start	Expanded End		Expanded lookup value	Sorted Start	Matching End
3	No	10.1.1.300	10.1.1.0	10.1.1.310		010.001.001.000	010.001.001.310		010.001.001.300	010.001.001.000	010.001.001.310
4			10.1.1.128	10.1.1.223		010.001.001.128	010.001.001.223			010.001.001.128	010.001.001.223
IP (3)

Cell Formulas
Range	Formula
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.

 

[Drew123]

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

 

[miroslav82]

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.

 

[Peter_SSs]

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")

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.