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
|