Range Vlookup

[deen]

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

 

[helene and gabor]

Hello Deen,

Is there an error on your post?
I think what you want is a list like:

abc.st to ajk.xz AG
nb.ert to nb.yt EMEA

a table from which immediate values could be located.

Best Regards,


Gabor Sebo

 

[helene and gabor]

Hello Deen


Maybe your IP numbers could have just 5 digits with no decimals for the
lookup procedure.

You could write if statements depending on the number of IP numbers that you
have. Lookup functions could also be used.

I see the following steps:

Take search IP number, cut it down to 5 digits with no decimals,use it in If
or Lookup statements to get your area designation.

Eg.

if(and(A1>=19216, A1<=19518), "AP", If.....



Best Regards,

Gabor Sebo

 

[Pete_UK]

Each full-stop in an IP address represents a power of 256, so:

192.168.1.4

as an example represents the number:

192*256*256*256 + 168*256*256 + 1*256 + 4

I suggested to Deen in an earlier post that he needs to convert his IP
addresses to proper numbers and then he will be able to search through
the ranges more easily.

Hope this helps.

Pete