find IP in range / find number between numbers

macropod · #1 06-14-2015, 05:25 AM

If you sort the data in columns F-H so the IP ranges are in ascending order, thus:

Code:

Internal-restricted    10.100.1.0       10.100.1.63
Internal-voip         10.100.1.64      10.100.1.127
Internet-Zone 2         83.1.25.0       83.1.25.255
Internet-Zone 1     109.11.93.192     109.11.92.255
DMZ                  192.168.20.0    192.168.20.255

in D13 you can use an array formula like:

Code:

=INDEX($G$2:$G$7,MATCH(TEXT(LEFT(C13,FIND(".",C13)),"000")&TEXT(MID(C13,FIND(".",C13)+1,FIND(".",C13,FIND(".",C13)+1)-1-FIND(".",C13)),"000")&TEXT(MID(C13,FIND(".",C13,FIND(".",C13)+1)+1,FIND(".",C13,FIND(".",C13,FIND(".",C13)+1)+1)-1-FIND(".",C13,FIND(".",C13)+1)),"000")&TEXT(MID(C13,FIND(".",C13,FIND(".",C13,FIND(".",C13)+1)+1)+1,LEN(C13)),"000"),TEXT(LEFT($H$2:$H$7,FIND(".",$H$2:$H$7)),"000")&TEXT(MID($H$2:$H$7,FIND(".",$H$2:$H$7)+1,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)-1-FIND(".",$H$2:$H$7)),"000")&TEXT(MID($H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)+1,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)+1)-1-FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)),"000")&TEXT(MID($H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)+1)+1,LEN($H$2:$H$7)),"000"),1))

Note: You should probably also interleave the valid ranges with the IP addresses that are invalid (e.g. 83.1.26.0 - 109.11.93.191), otherwise you may end up with false matches. If you can't do that and there is a risk of IP addresses that are invalid being input, a rather more complex version of the above formula would be required.

gn28 · #2 06-14-2015, 11:59 AM

Quote:

Originally Posted by macropod

If you sort the data in columns F-H so the IP ranges are in ascending order, thus:

Code:

Internal-restricted    10.100.1.0       10.100.1.63
Internal-voip         10.100.1.64      10.100.1.127
Internet-Zone 2         83.1.25.0       83.1.25.255
Internet-Zone 1     109.11.93.192     109.11.92.255
DMZ                  192.168.20.0    192.168.20.255

in D13 you can use an array formula like:

Code:

=INDEX($G$2:$G$7,MATCH(TEXT(LEFT(C13,FIND(".",C13)),"000")&TEXT(MID(C13,FIND(".",C13)+1,FIND(".",C13,FIND(".",C13)+1)-1-FIND(".",C13)),"000")&TEXT(MID(C13,FIND(".",C13,FIND(".",C13)+1)+1,FIND(".",C13,FIND(".",C13,FIND(".",C13)+1)+1)-1-FIND(".",C13,FIND(".",C13)+1)),"000")&TEXT(MID(C13,FIND(".",C13,FIND(".",C13,FIND(".",C13)+1)+1)+1,LEN(C13)),"000"),TEXT(LEFT($H$2:$H$7,FIND(".",$H$2:$H$7)),"000")&TEXT(MID($H$2:$H$7,FIND(".",$H$2:$H$7)+1,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)-1-FIND(".",$H$2:$H$7)),"000")&TEXT(MID($H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)+1,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)+1)-1-FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)),"000")&TEXT(MID($H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7,FIND(".",$H$2:$H$7)+1)+1)+1,LEN($H$2:$H$7)),"000"),1))

Note: You should probably also interleave the valid ranges with the IP addresses that are invalid (e.g. 83.1.26.0 - 109.11.93.191), otherwise you may end up with false matches. If you can't do that and there is a risk of IP addresses that are invalid being input, a rather more complex version of the above formula would be required.

Hi there,
First of all, thanks for the helping hand.

Was I suppose to do something special with the formula?
I am getting "a value used in the formula is of the wrong data type" error.

Thanks in advance

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to find number of coma and then add that number of rows in word using macro?	PRA007	Word VBA	7	05-27-2015 10:45 PM
Help!! find two number recurring numbers in row??	carrolld2	Excel	5	06-28-2014 05:43 PM
Find and Delete Rows based on a range	damaniam	Excel Programming	2	03-12-2014 06:06 AM
Find and Replace using Excel range	dmarie123	Word VBA	15	04-02-2013 07:54 AM
Find and Replace within range	anil3b2	Word VBA	3	12-01-2010 02:35 AM