Vlookup in IP Range

D

dday0707

New Member
Joined
May 30, 2012
Messages
5
Hello,

I am trying to use VLOOKUP to find an IP from a list of IP Range

E.g
Let say I want to find IP 10.4.4.127 and want to return site "D" as value from following list

Site Start IP Range End IP Range

A 10.1.1.1 - 10.1.1.255 (10.1.1.1,10.1.1.2,10.1.1.3,......UP 10.1.1.255)
B 10.2.2.1 - 10.2.2.255
C 10.3.3.1 - 10.3.3.255
D 10.4.4.1 - 10.4.4.255
E 10.5.5.1 - 10.5.5.255

Thanks in advance
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
You can do this using index and match.

First, split your entries into columns B for the starting IP addres and one for the second using something like =left(A1,8) for the starting IP number and =right(A1,10) for the end number.

Then just use an non exact match (ie. =MATCH($C$1,$B$1:$B$5,1)) and it will find the IP address directly below the one you're looking for. Then just index the range and use match to pick out the right position in that range. You can use something like =INDEX($A$1:$A$5,$D$1,) where D1 contains your match formula.

Good luck!
 
Upvote 0
Even better! You don't need to split out the starting IP addresses into column B. You can use an array formula within your match formula. Try this formula instead of the mathc formula above and hold ctrl+shift when you hit enter to make it work

=MATCH($C$1,LEFT($A$1:$A$5,8),1)

Gook luck!
 
Upvote 0
Thanks for your prompt reply.
I tried it works to some extent but not 100% as I want.

I have an exhaustive IP log which I need to match from a database.

Also IP address starts from 10.0.0.0 to 10.255.255.255
IP are subnet in blocks of either 510 or 1022 host

example

10.0.1.1 - 10.0.1.254 510
10.0.22.1 - 10.0.23.254 510
10.4.28.1 - 10.4.31.254 1022
10.6.8.1 - 10.6.11.254 1022
 
Upvote 0
Does this help?

Sheet2
Excel Workbook
ABCDEFG
1A10.1.1.110.1.1.2551011110.4.4.127D
2B10.2.2.110.2.2.25510221
3C10.3.3.110.3.3.25510331
4D10.4.4.110.4.4.25510441
5E10.5.5.110.5.5.25510551
Sheet
 
Upvote 0
Unfortunately it does not work

Sorry wanted to attach a snapshot but cant figure it out

-- removed inline image ---
 
Last edited:
Upvote 0
To post data
You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

or you can use internal side (dropbox) or Google Docs
 
Upvote 0
<table border="0" cellpadding="0" cellspacing="0" width="1097"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5595; width:115pt" span="2" width="153"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="width:48pt" span="10" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt;font-size:11.0pt; color:#E26B0A;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #F79646; border-right:none;border-bottom:.5pt solid #F79646;border-left:none" height="20" width="64">Site</td> <td style="width:115pt;font-size:11.0pt;color:#E26B0A;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #F79646;border-right:none; border-bottom:.5pt solid #F79646;border-left:none" width="153">start IP</td> <td style="width:115pt;font-size:11.0pt;color:#E26B0A;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #F79646;border-right:none; border-bottom:.5pt solid #F79646;border-left:none" width="153">End IP</td> <td class="xl65" style="width:65pt;font-size:10.0pt;color:navy; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Courier New";border-top:.5pt solid #F79646;border-right: none;border-bottom:.5pt solid #F79646;border-left:none" width="87">Substitute</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">10.0.8.1</td> <td style="width:48pt" width="64">GGG</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#FDE9D9;mso-pattern:#FDE9D9 none" height="23">AAA</td> <td class="xl63" style="width:115pt;font-size:10.0pt;color:navy; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Courier New";border:.5pt solid windowtext;background:#FDE9D9; mso-pattern:#FDE9D9 none" width="153">10.0.0.1</td> <td class="xl64" style="border-left:none;font-size:10.0pt;color:navy; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Courier New";border:.5pt solid windowtext;background:#FDE9D9; mso-pattern:#FDE9D9 none">10.0.1.254</td> <td class="xl65" colspan="3" style="mso-ignore:colspan;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:none;background:#FDE9D9; mso-pattern:#FDE9D9 none">=SUBSTITUTE(D4,".","")</td> <td>
</td> <td colspan="7" style="mso-ignore:colspan">=INDEX($C$4:$C$10,MATCH(SUBSTITUTE(I3,".",""),$F$4:$F$10,1))</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri" height="23">BBB</td> <td class="xl63" style="border-top:none;width:115pt" width="153">10.0.2.1</td> <td class="xl64" style="border-top:none;border-left:none;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext">10.0.3.254</td> <td class="xl65" style="font-size:10.0pt;color:navy;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:"Courier New"; border:none">10021</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#FDE9D9;mso-pattern:#FDE9D9 none" height="23">CCC</td> <td class="xl63" style="border-top:none;width:115pt;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext; background:#FDE9D9;mso-pattern:#FDE9D9 none" width="153">10.0.4.1</td> <td class="xl64" style="border-top:none;border-left:none;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext; background:#FDE9D9;mso-pattern:#FDE9D9 none">10.0.5.254</td> <td class="xl65" style="font-size:10.0pt;color:navy;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:"Courier New"; border:none;background:#FDE9D9;mso-pattern:#FDE9D9 none">10041</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri" height="23">DDD</td> <td class="xl63" style="border-top:none;width:115pt" width="153">10.0.6.1</td> <td class="xl64" style="border-top:none;border-left:none;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext">10.0.7.254</td> <td class="xl65" style="font-size:10.0pt;color:navy;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:"Courier New"; border:none">10061</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#FDE9D9;mso-pattern:#FDE9D9 none" height="23">EEE</td> <td class="xl63" style="border-top:none;width:115pt;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext; background:#FDE9D9;mso-pattern:#FDE9D9 none" width="153">10.0.8.1</td> <td class="xl64" style="border-top:none;border-left:none;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext; background:#FDE9D9;mso-pattern:#FDE9D9 none">10.0.9.254</td> <td class="xl65" style="font-size:10.0pt;color:navy;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:"Courier New"; border:none;background:#FDE9D9;mso-pattern:#FDE9D9 none">10081</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri" height="23">FFF</td> <td class="xl63" style="border-top:none;width:115pt" width="153">10.0.12.1</td> <td class="xl64" style="border-top:none;border-left:none;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext">10.0.13.254</td> <td class="xl65" style="font-size:10.0pt;color:navy;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:"Courier New"; border:none">100121</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="mso-height-source:userset;height:17.25pt" height="23"> <td style="height:17.25pt;font-size:11.0pt;color:#E26B0A; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:none;border-right:none;border-bottom: .5pt solid #F79646;border-left:none;background:#FDE9D9;mso-pattern:#FDE9D9 none" height="23">GGG</td> <td class="xl63" style="border-top:none;width:115pt;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext; background:#FDE9D9;mso-pattern:#FDE9D9 none" width="153">10.0.14.1</td> <td class="xl64" style="border-top:none;border-left:none;font-size:10.0pt; color:navy;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:"Courier New";border:.5pt solid windowtext; background:#FDE9D9;mso-pattern:#FDE9D9 none">10.0.15.254</td> <td class="xl65" style="font-size:10.0pt;color:navy;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:"Courier New"; border-top:none;border-right:none;border-bottom:.5pt solid #F79646; border-left:none;background:#FDE9D9;mso-pattern:#FDE9D9 none">100141</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="mso-height-source:userset;height:27.75pt" height="37"> <td style="height:27.75pt" height="37">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 
Upvote 0
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Site</td><td style=";">start IP</td><td style=";">End IP</td><td style=";">Substitute</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">10.0.8.1</td><td style=";">EEE</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">AAA</td><td style=";">10.0.0.1</td><td style=";">10.0.1.254</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">10.0.5.1</td><td style=";">CCC</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">BBB</td><td style=";">10.0.2.1</td><td style=";">10.0.3.254</td><td style="text-align: right;;">10021</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">10.0.7.1</td><td style=";">DDD</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">CCC</td><td style=";">10.0.4.1</td><td style=";">10.0.5.254</td><td style="text-align: right;;">10041</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">10.0.0.1</td><td style=";">AAA</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">DDD</td><td style=";">10.0.6.1</td><td style=";">10.0.7.254</td><td style="text-align: right;;">10061</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">10.0.13.1</td><td style=";">FFF</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">EEE</td><td style=";">10.0.8.1</td><td style=";">10.0.9.254</td><td style="text-align: right;;">10081</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">FFF</td><td style=";">10.0.12.1</td><td style=";">10.0.13.254</td><td style="text-align: right;;">100121</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">GGG</td><td style=";">10.0.14.1</td><td style=";">10.0.15.254</td><td style="text-align: right;;">100141</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>
Sheet4​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">H1</th><td style="text-align:left">=INDEX($A$2:$A$8,MATCH(SUBSTITUTE(G1,".","")+0,$D$2:$D$8,1))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">D2</th><td style="text-align:left">=SUBSTITUTE(B2,".","")+0</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA to filter IP addresses based on first 2 octets
2
Replies
16
Views
2K
addistexan
A
H
  • Question Question
How to Use VLOOKUP to Copy Data Between Sheets Based on a Code?
Replies
3
Views
172
Alex Blakenburg
A
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
192
kevin9999
K
D
  • Solved
Need help with random number generator
Replies
3
Views
804
Fluff
Fluff
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
284
Lux Aeterna
L

Forum statistics

Threads
1,226,392
Messages
6,190,768
Members
453,615
Latest member
robbieb29

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