#### bigblue40

##### Board Regular
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 200 10.24.200.201
Vlan 300 10.24.300.201
Vlan 400 10.24.400.201
Vlan 500 10.24.500.201

### 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
bigblue40, Good afternoon.

#### bigblue40

##### Board Regular
hi,

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

#### Marcílio_Lobão

##### Well-known Member
bigblue40, Good afternoon.

Scenario:

 A B 1 VLAN N IP Addy 2 Vlan 200 10.24.200.201 3 Vlan 300 10.24.300.201 4 Vlan 400 10.24.400.201 5 Vlan 500 10.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
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

Replies
8
Views
318
Replies
4
Views
85
Replies
0
Views
3K
Replies
36
Views
5K
Replies
1
Views
243