IP Address Lookup

bigblue40

Board Regular
Joined
Aug 3, 2003
Messages
102
Hi
I have a spreadsheet with 500 lines of IP addresses and I want to have an additional lookup of the Vlan names as below

I need to look at the third Octet of the IP address ie the 200, 300, 400 of the IP address etc to then return the VLAN name, ie Vlan200, Vlan300 etc.

Hope this is clear, thanks in advance for the help...

Column A Column B
VLAN N IP Addy
Vlan 200 10.24.200.201
Vlan 300 10.24.300.201
Vlan 400 10.24.400.201
Vlan 500 10.24.500.201
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
697
Platform
  1. Windows
bigblue40, Good afternoon.

All your IP addresses have the same MASK? 10.24.xxx.201?
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
697
Platform
  1. Windows
bigblue40, Good afternoon.

Scenario:

AB
1VLAN N
IP Addy
2Vlan 20010.24.200.201
3Vlan 30010.24.300.201
4Vlan 40010.24.400.201
5Vlan 50010.24.500.201

<tbody>
</tbody>

I imagine that this VLAN 200 for 10.24.200.201 with the same 200 is only an example, isn't?

Try to use:

D1 --> SEARCH NUMBER --> 200

D3 --> FORMULA --> =LOOKUP(2,1/(MID($B$2:$B$5,7,3)=$D$1&""),$A$2:$A$5)

Is that what you want?

I hope it helps.
 

bigblue40

Board Regular
Joined
Aug 3, 2003
Messages
102
PErfect thanks MArcilio, much appreciated.

Vlan 200 10.24.200.201 200 Vlan 200
Vlan 300 10.24.300.201 300 Vlan 300
Vlan 400 10.24.400.201 200 Vlan 200
Vlan 500 10.24.500.201 500 Vlan 500
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,832
Members
409,839
Latest member
akashsadhu
Top