Vlookup Question

[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

 

[Jacob Skaria]

Its pretty easy to do this using a macro..If you are looking for a formula
try the below (bit lengthy)

In Result sheet cell A1 enter the IP number

In cell B1 enter the below formula. Please note that this is an array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

The result for the sample you pasted would be as below (which is different
from what you have posted)

192.170.30.30 AG
194.168.10.20 AG
10.131.37.20 AP
10.170.255.255 #N/A
10.129.36.8 AP



=INDEX('IP Range'!$C$1:$C$10,
MATCH(1,(TEXT(LEFT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")&
TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),7,6),"000")&
TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),13,9),"000")&
TEXT(RIGHT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")<=
TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")&
TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")&
TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")&
TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000"))*(
TEXT(LEFT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")&
TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),7,6),"000")&
TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),13,9),"000")&
TEXT(RIGHT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")>=
TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")&
TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")&
TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")&
TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000")),0))

 

[Deen]

Hi jacob,

Thanks for your great help, Is look good, But i'm confused with formula,
Could you please help me to how i can do in macro.

Thanks in advance
Deen

 

[Deen]

Hi Jacob,

Sorry for delay response,

Is not working,