If text found in column write a different text(label) to a different column

buschman007

New Member
Joined
May 30, 2018
Messages
6
So I have a list of network addresses (IPv4). I need to add a label for these subnets.


My legend will look like this:
10.0.0.0 = Corp Data VLAN
10.1.0.0 = Corp Voice VLAN
10.0.10.0 = VPN VLAN
10.3.0.0 = Baltimore Branch Data VLAN
10.3.1.0 = Baltimore Branch Voice VLAN
10.4.0.0 = DC Branch Data VLAN
10.4.1.0 = DC Branch Voice VLAN
etc.


So I have a list of Network Addresses currently. I need a formula that allows me to populate the "Network Name" column with the legend above. How can I best accomplish this? I'm not finding formula that allow me to do one offs, but not a list like the legend above. let me know if anyone can help.

Thanks,
Mike
Network AddressNetwork Name
10.0.0.0Corp Data VLAN
10.0.0.0Corp Data VLAN
10.0.0.0Corp Data VLAN
10.1.0.0Corp Voice VLAN
10.0.10.0VPN VLAN
10.0.10.0VPN VLAN
10.0.10.0VPN VLAN
10.3.0.0Baltimore Branch Data VLAN
10.3.0.0Baltimore Branch Data VLAN
10.3.0.0Baltimore Branch Data VLAN
10.3.0.0Baltimore Branch Data VLAN
10.3.0.0Baltimore Branch Data VLAN
10.3.0.0Baltimore Branch Data VLAN

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Hurkk

New Member
Joined
Jan 17, 2018
Messages
13
I suggest converting your IP address into numbers:

=SUBSTITUTE(Q9,".","")+0 apply this formula to your legend and list Example: 10.0.0.0 --> 10000

Once you converted the addresses simply use a Vlookup function:

=VLOOKUP(C2,$P$9:$R$15,2,FALSE)

Regards,

Hurkk
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Can you not separate your Legend into 2 columns like below, then simply use VLOOKUP?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Legend</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">10.1.0.0</td><td style=";">Corp Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">10.3.1.0</td><td style=";">Baltimore Branch Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10.4.0.0</td><td style=";">DC Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10.4.1.0</td><td style=";">DC Branch Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Network Address</td><td style=";">Network Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">10.1.0.0</td><td style=";">Corp Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet67</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A12,A$2:B$8,2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Too late to Edit...

If you separate the Legend into 2 columns, use B12 formula copied down.

If you can Not separate the Legend, use D12 formula copied down, remember to confirm with CSE (instructions below):

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Legend</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td><td style="text-align: right;;"></td><td style=";">10.0.0.0 = Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">10.1.0.0</td><td style=";">Corp Voice VLAN</td><td style="text-align: right;;"></td><td style=";">10.1.0.0 = Corp Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td><td style="text-align: right;;"></td><td style=";">10.0.10.0 = VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">10.3.0.0 = Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">10.3.1.0</td><td style=";">Baltimore Branch Voice VLAN</td><td style="text-align: right;;"></td><td style=";">10.3.1.0 = Baltimore Branch Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10.4.0.0</td><td style=";">DC Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">10.4.0.0 = DC Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10.4.1.0</td><td style=";">DC Branch Voice VLAN</td><td style="text-align: right;;"></td><td style=";">10.4.1.0 = DC Branch Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</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: rgb(22,17,32);text-align: center;">10</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: rgb(22,17,32);text-align: center;">11</td><td style=";">Network Address</td><td style=";">Network Name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td><td style="text-align: right;;"></td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td><td style="text-align: right;;"></td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td><td style="text-align: right;;"></td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">10.1.0.0</td><td style=";">Corp Voice VLAN</td><td style="text-align: right;;"></td><td style=";">Corp Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td><td style="text-align: right;;"></td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td><td style="text-align: right;;"></td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td><td style="text-align: right;;"></td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td><td style="text-align: right;;"></td><td style=";">Baltimore Branch Data VLAN</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet67</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A12,A$2:B$8,2,0</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D12</th><td style="text-align:left">{=INDEX(<font color="Blue">MID(<font color="Red">D$2:D$8,FIND(<font color="Green">"=",D$2:D$8</font>)+2,255</font>),MATCH(<font color="Red">A12,LEFT(<font color="Green">D$2:D$8,FIND(<font color="Purple">"=",D$2:D$8</font>)-2</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

buschman007

New Member
Joined
May 30, 2018
Messages
6

ADVERTISEMENT

Jtakw, thanks for the suggestion. I can separate my legend however I'll need to put it on a different sheet. Researching how to add the different sheet into your vlookup code. Thanks again for the assistance.

Thanks,
Mike
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Jtakw, thanks for the suggestion. I can separate my legend however I'll need to put it on a different sheet. Researching how to add the different sheet into your vlookup code. Thanks again for the assistance.

Thanks,
Mike

You're welcome, welcome to the forum.

In 2 separate sheets, the formula will look something like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Network Address</td><td style=";">Network Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">10.1.0.0</td><td style=";">Corp Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Network Name</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A2,Legend!A$2:B$8,2,0</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Legend</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10.0.0.0</td><td style=";">Corp Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">10.1.0.0</td><td style=";">Corp Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">10.0.10.0</td><td style=";">VPN VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">10.3.0.0</td><td style=";">Baltimore Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">10.3.1.0</td><td style=";">Baltimore Branch Voice VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10.4.0.0</td><td style=";">DC Branch Data VLAN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10.4.1.0</td><td style=";">DC Branch Voice VLAN</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Legend</p><br /><br />
 

buschman007

New Member
Joined
May 30, 2018
Messages
6

ADVERTISEMENT

I got it to work with the following, but looks like I was on the right track.

=VLOOKUP(I2,SubnetLegend!A2:B10,2,0)

Thanks again for your help on this one.

Mike
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're welcome, glad to help.

You'll need to add "absolute" references for the Legend table in your formula to anchor down the A2:B10, otherwise, the references will change as you drag the formula down:

=VLOOKUP(I2,SubnetLegend!A$2:B$10,2,0)
 

buschman007

New Member
Joined
May 30, 2018
Messages
6
Scratch my last post. I now see the addition of the $ is necessary. once I got past 9 rows things stopped working. So Jtakw formula is completely correct.

=VLOOKUP(I5448,SubnetLegend!A$2:B$10,2,0)

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top