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

buschman007

New Member
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 Address Network Name 10.0.0.0 Corp Data VLAN 10.0.0.0 Corp Data VLAN 10.0.0.0 Corp Data VLAN 10.1.0.0 Corp Voice VLAN 10.0.10.0 VPN VLAN 10.0.10.0 VPN VLAN 10.0.10.0 VPN VLAN 10.3.0.0 Baltimore Branch Data VLAN 10.3.0.0 Baltimore Branch Data VLAN 10.3.0.0 Baltimore Branch Data VLAN 10.3.0.0 Baltimore Branch Data VLAN 10.3.0.0 Baltimore Branch Data VLAN 10.3.0.0 Baltimore Branch Data VLAN

<tbody>
</tbody>

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

=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
Hi,

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

jtakw

Well-known Member
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>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

buschman007

New Member

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
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=";">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

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

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
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!