vlookup based on IP range

Hi Every One,

I hope you all are doing well, I required your help to resolve my issue in
excel.

On single worksheet i have two different sheet name as 1.IP Range 2. Result.

Question:
EG:IP Range

Start IP End IP
Area

192.168.1.4 195.182.254.254 AG
10.15.33.10 10.18.56.254
EMEA
10.128.33.5 10.132.40.60 AP

Here it will Continued as well.

On Result sheet i required result like:

IP Area

192.170.30.30 AG
194.168.10.20 AG
10.131.37.20 EMEA
10.170.255.255 NA
10.129.36.8 AP

I required result on area column. Here we have more than 50000 IP's but very
difficult to find the area.

Could any one please help me on this issue. How i can resolve the issue


Thanks in advance
Deen

You will have to convert those IP addresses to actual numbers (each
dot represents a power of 256) and then sort the data by start address
to enable you to determine the ranges correctly. Then you would be
able to find the nearest (lower or equal) start address using VLOOKUP,
and then check the end address to see that it is not lower than your
sought address.

Hope this helps.

Pete


On Apr 15, 6:52*am, Deen wrote:
Hi Every One,

I hope you all are doing well, I required your help to resolve my issue in
excel.

On single worksheet i have two different sheet name as 1.IP Range 2. Result.

Question:
EG:IP Range

Start IP End IP
Area

192.168.1.4 195.182.254.254 AG
10.15.33.10 10.18.56.254
EMEA
10.128.33.5 10.132.40.60 AP

Here it will Continued as well.

On Result sheet i required result like:

IP Area

192.170.30.30 AG
194.168.10.20 AG
10.131.37.20 EMEA
10.170.255.255 NA
10.129.36.8 AP

I required result on area column. Here we have more than 50000 IP's but very
difficult to find the area.

Could any one please help me on this issue. How i can resolve the issue

Thanks in advance
Deen

Hello Pete,

I have done the same as you said, But no luck.... Could you please explain

Regards,
Deen



"Pete_UK" wrote:

You will have to convert those IP addresses to actual numbers (each
dot represents a power of 256) and then sort the data by start address
to enable you to determine the ranges correctly. Then you would be
able to find the nearest (lower or equal) start address using VLOOKUP,
and then check the end address to see that it is not lower than your
sought address.

Hope this helps.

Pete


On Apr 15, 6:52 am, Deen wrote:
Hi Every One,

I hope you all are doing well, I required your help to resolve my issue in
excel.

On single worksheet i have two different sheet name as 1.IP Range 2. Result.

Question:
EG:IP Range

Start IP End IP
Area

192.168.1.4 195.182.254.254 AG
10.15.33.10 10.18.56.254
EMEA
10.128.33.5 10.132.40.60 AP

Here it will Continued as well.

On Result sheet i required result like:

IP Area

192.170.30.30 AG
194.168.10.20 AG
10.131.37.20 EMEA
10.170.255.255 NA
10.129.36.8 AP

I required result on area column. Here we have more than 50000 IP's but very
difficult to find the area.

Could any one please help me on this issue. How i can resolve the issue

Thanks in advance
Deen

.