Vlookup in IP Range

[dday0707]

Hello,

I am trying to use VLOOKUP to find an IP from a list of IP Range

E.g
Let say I want to find IP 10.4.4.127 and want to return site "D" as value from following list

Site Start IP Range End IP Range

A 10.1.1.1 - 10.1.1.255 (10.1.1.1,10.1.1.2,10.1.1.3,......UP 10.1.1.255)
B 10.2.2.1 - 10.2.2.255
C 10.3.3.1 - 10.3.3.255
D 10.4.4.1 - 10.4.4.255
E 10.5.5.1 - 10.5.5.255

Thanks in advance

 

[forumwurm]

Hi Robert, Hi all

that is a nice solution and it seems to work for full ip ranges. However I'm struggling with smaller ranges.

Site	start IP	End IP	Substitute	10.0.8.1	EEE
AAA	10.0.0.1	10.0.1.254	10.0.5.1	CCC
BBB	10.0.2.1	10.0.3.254	10.0.7.1	DDD
CCC	10.0.4.1	10.0.5.254	10.0.0.1	AAA
DDD	10.0.6.1	10.0.7.254	10.0.13.1	FFF
EEE	10.10.10.0
FFF	10.10.10.16
GGG	10.10.10.128

<tbody>
</tbody><thead>
[TH="align: center"]
A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Net
IP-Range[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10021[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10041[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10061[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10.10.10.165[/TD]

</tbody>

The IP in Cell H9 (10.10.10.165) should return the site GGG, but with the current calculation it would fall under FFF.

Any idea/hint how I could solve this? I guess we would not only use the "Start IP" but also the "End IP".

 

[forumwurm]

I did some tests by having always three digits for the first 3 segments, meaning 10.10.10.x was translated to 010.010.010.x but still not working obviously.

My new approach would be, but I'm not the specialist in Excel and do know now how to achieve this:

1. Translate H9 (10.10.10.165) to
   a. ABC = 101010
   b. D = 165
2. find all related ABC in B2-B8 which would be in this case B6, B7, B8
3. find the next smaller "D" in B6-B8 compared to the previously evaluated D (from H9), meaning
   what is the next smaller number from 165? 128 or 16 or 0 (D of B6-B8) which would be 128 and returning Site=GGG (A8)

Any excel specialist who can figure this out? best case in one cell without any additional intermediate steps.

Thank you very much