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

B

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Network Address[/TD]
[TD]Network Name[/TD]
[/TR]
[TR]
[TD]10.0.0.0[/TD]
[TD]Corp Data VLAN[/TD]
[/TR]
[TR]
[TD]10.0.0.0[/TD]
[TD]Corp Data VLAN[/TD]
[/TR]
[TR]
[TD]10.0.0.0[/TD]
[TD]Corp Data VLAN[/TD]
[/TR]
[TR]
[TD]10.1.0.0[/TD]
[TD]Corp Voice VLAN[/TD]
[/TR]
[TR]
[TD]10.0.10.0[/TD]
[TD]VPN VLAN[/TD]
[/TR]
[TR]
[TD]10.0.10.0[/TD]
[TD]VPN VLAN[/TD]
[/TR]
[TR]
[TD]10.0.10.0[/TD]
[TD]VPN VLAN[/TD]
[/TR]
[TR]
[TD]10.3.0.0[/TD]
[TD]Baltimore Branch Data VLAN[/TD]
[/TR]
[TR]
[TD]10.3.0.0[/TD]
[TD]Baltimore Branch Data VLAN[/TD]
[/TR]
[TR]
[TD]10.3.0.0[/TD]
[TD]Baltimore Branch Data VLAN[/TD]
[/TR]
[TR]
[TD]10.3.0.0[/TD]
[TD]Baltimore Branch Data VLAN[/TD]
[/TR]
[TR]
[TD]10.3.0.0[/TD]
[TD]Baltimore Branch Data VLAN[/TD]
[/TR]
[TR]
[TD]10.3.0.0[/TD]
[TD]Baltimore Branch Data VLAN[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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
 
Upvote 0
Hi,

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


Book1
AB
1Legend
210.0.0.0Corp Data VLAN
310.1.0.0Corp Voice VLAN
410.0.10.0VPN VLAN
510.3.0.0Baltimore Branch Data VLAN
610.3.1.0Baltimore Branch Voice VLAN
710.4.0.0DC Branch Data VLAN
810.4.1.0DC Branch Voice VLAN
9
10
11Network AddressNetwork Name
1210.0.0.0Corp Data VLAN
1310.0.0.0Corp Data VLAN
1410.0.0.0Corp Data VLAN
1510.1.0.0Corp Voice VLAN
1610.0.10.0VPN VLAN
1710.0.10.0VPN VLAN
1810.0.10.0VPN VLAN
1910.3.0.0Baltimore Branch Data VLAN
2010.3.0.0Baltimore Branch Data VLAN
2110.3.0.0Baltimore Branch Data VLAN
2210.3.0.0Baltimore Branch Data VLAN
2310.3.0.0Baltimore Branch Data VLAN
2410.3.0.0Baltimore Branch Data VLAN
Sheet67
Cell Formulas
RangeFormula
B12=VLOOKUP(A12,A$2:B$8,2,0)
 
Upvote 0
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):


Book1
ABCD
1Legend
210.0.0.0Corp Data VLAN10.0.0.0 = Corp Data VLAN
310.1.0.0Corp Voice VLAN10.1.0.0 = Corp Voice VLAN
410.0.10.0VPN VLAN10.0.10.0 = VPN VLAN
510.3.0.0Baltimore Branch Data VLAN10.3.0.0 = Baltimore Branch Data VLAN
610.3.1.0Baltimore Branch Voice VLAN10.3.1.0 = Baltimore Branch Voice VLAN
710.4.0.0DC Branch Data VLAN10.4.0.0 = DC Branch Data VLAN
810.4.1.0DC Branch Voice VLAN10.4.1.0 = DC Branch Voice VLAN
9
10
11Network AddressNetwork Name
1210.0.0.0Corp Data VLANCorp Data VLAN
1310.0.0.0Corp Data VLANCorp Data VLAN
1410.0.0.0Corp Data VLANCorp Data VLAN
1510.1.0.0Corp Voice VLANCorp Voice VLAN
1610.0.10.0VPN VLANVPN VLAN
1710.0.10.0VPN VLANVPN VLAN
1810.0.10.0VPN VLANVPN VLAN
1910.3.0.0Baltimore Branch Data VLANBaltimore Branch Data VLAN
2010.3.0.0Baltimore Branch Data VLANBaltimore Branch Data VLAN
2110.3.0.0Baltimore Branch Data VLANBaltimore Branch Data VLAN
2210.3.0.0Baltimore Branch Data VLANBaltimore Branch Data VLAN
2310.3.0.0Baltimore Branch Data VLANBaltimore Branch Data VLAN
2410.3.0.0Baltimore Branch Data VLANBaltimore Branch Data VLAN
Sheet67
Cell Formulas
RangeFormula
B12=VLOOKUP(A12,A$2:B$8,2,0)
D12{=INDEX(MID(D$2:D$8,FIND("=",D$2:D$8)+2,255),MATCH(A12,LEFT(D$2:D$8,FIND("=",D$2:D$8)-2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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
 
Upvote 0
buschman007 said:
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
Click to expand...

You're welcome, welcome to the forum.

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


Book1
AB
1Network AddressNetwork Name
210.0.0.0Corp Data VLAN
310.0.0.0Corp Data VLAN
410.0.0.0Corp Data VLAN
510.1.0.0Corp Voice VLAN
610.0.10.0VPN VLAN
710.0.10.0VPN VLAN
810.0.10.0VPN VLAN
910.3.0.0Baltimore Branch Data VLAN
1010.3.0.0Baltimore Branch Data VLAN
1110.3.0.0Baltimore Branch Data VLAN
1210.3.0.0Baltimore Branch Data VLAN
1310.3.0.0Baltimore Branch Data VLAN
1410.3.0.0Baltimore Branch Data VLAN
Network Name
Cell Formulas
RangeFormula
B2=VLOOKUP(A2,Legend!A$2:B$8,2,0)



Book1
AB
1Legend
210.0.0.0Corp Data VLAN
310.1.0.0Corp Voice VLAN
410.0.10.0VPN VLAN
510.3.0.0Baltimore Branch Data VLAN
610.3.1.0Baltimore Branch Voice VLAN
710.4.0.0DC Branch Data VLAN
810.4.1.0DC Branch Voice VLAN
Legend
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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