Vlookup Question

D

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
 
J

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))
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup based on IP range 3
Vlookup with difference between two data 0
Range Vlookup 3
Range Vlookup 1
Range Vlookup 1

Top