IP Address Lookup

[bigblue40]

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

 

[Marcílio_Lobão]

bigblue40, Good afternoon.

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

 

[bigblue40]

hi,

the first two are the same, ie the 10.24 the last is different.

 

[Marcílio_Lobão]

bigblue40, Good afternoon.

Scenario:

[TABLE="class: grid, width: 252"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]VLAN N
[/TD]
[TD="align: center"]IP Addy[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Vlan 200[/TD]
[TD]10.24.200.201[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Vlan 300[/TD]
[TD]10.24.300.201[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Vlan 400[/TD]
[TD]10.24.400.201[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Vlan 500[/TD]
[TD]10.24.500.201[/TD]
[/TR]
</tbody>[/TABLE]

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]

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