CODEWITHSUNDEEP
arraysexcel-formulamatch
Drew
Drew

Reputation: 43

Excel Lookup IP addresses in multiple ranges

I am trying to find a formula for column A that will check an IP address in column B and find if it falls into a range (or between) 2 addresses in two other columns C and D.

E.G.

     A           B             C             D
+---------+-------------+-------------+------------+
| valid?  | address     | start       | end        |
+---------+-------------+-------------+------------+
| yes     | 10.1.1.5    | 10.1.1.0    | 10.1.1.31  |
| Yes     | 10.1.3.13   | 10.1.2.16   | 10.1.2.31  |
| no      | 10.1.2.7    | 10.1.1.128  | 10.1.1.223 |
| no      | 10.1.1.62   | 10.1.3.0    | 10.1.3.127 |
| yes     | 10.1.1.9    | 10.1.4.0    | 10.1.4.255 |
| no      | 10.1.1.50   | …           | …          |
| yes     | 10.1.1.200  |             |            |
+---------+-------------+-------------+------------+

This is supposed to represent an Excel table with 4 columns a heading and 7 rows as an example. I can do a lateral check with 

=IF(AND((B3>C3),(B3 < D3)),"yes","no")

which only checks 1 address against the range next to it. I need something that will check the 1 IP address against all of the ranges. i.e. rows 1 to 100.

This is checking access list rules against routes to see if I can eliminate redundant rules... but has other uses if I can get it going. To make it extra special I can not use VBA macros to get it done.

I'm thinking some kind of index match to look it up in an array but not sure how to apply it. I don't know if it can even be done. Good luck.

Upvotes: 0

Views: 12042

Answers (2)

Lana B
Lana B

Reputation: 496

You will need helper columns.

  • Organise your data as outlined in the picture.
  • Split address, start and end into columns by comma (ribbon menu Data=>Text To Columns).
  • Above the start/end parts, calculate MIN FOR START, and MAX FOR END for all split text parts (i.e. MIN(K5:K1000) .

FORMULAS:

VALIDITY formula - copy into cell D5, and drag down: 

=IF(AND(B6>$I$1,B6<$O$1),"In",
   IF(OR(B6<$I$1,B6>$O$1),"Out",
     IF(B6=$I$1,
       IF(C6<$J$1, "Out",
         IF( C6>$J$1, "In",
           IF( D6<$K$1,  "Out",
             IF( D6>$K$1, "In",
               IF(E6>=$L$1, "In", "Out"))))),
     IF(B6=$O$1,
       IF(C6>$P$1, "Out",
         IF( C6<$P$1, "In",
           IF( D6>$Q$1,  "Out",
             IF( D6<$Q$1, "In",
               IF(E6<=$R$1, "In", "Out") ))))   )
 )))

SOLUTION

Upvotes: 3

UndeadBob
UndeadBob

Reputation: 1129

Ok, so I've been tracking this problem since my initial comment, but have not taken the time to answer because just like Lana B:

I like a good puzzle, but it's not a good use of time if i have to keep guessing

+1 to Lana for her patience and effort on this question.

However, IP addressing is something I deal with regularly, so I decided to tackle this one for my own benefit. Also, no offense, but getting the MIN of the start and the MAX of the end is wrong. This will not account for gaps in the IP white-list. As I mentioned, this required 15 helper columns and my result is simply 1 or 0 corresponding to In or Out respectively. Here is a screenshot (with formulas shown below each column):

Valid IP address worksheet screenshot

The formulas in F2:J2 are:

=NUMBERVALUE(MID(B2,1,FIND(".",B2)-1))              
=NUMBERVALUE(MID(B2,FIND(".",B2)+1,FIND(".",B2,FIND(".",B2)+1)-1-FIND(".",B2)))         
=NUMBERVALUE(MID(B2,FIND(".",B2,FIND(".",B2)+1)+1,FIND(".",B2,FIND(".",B2,FIND(".",B2)+1)+1)-1-FIND(".",B2,FIND(".",B2)+1)))        
=NUMBERVALUE(MID(B2,FIND(".",B2,FIND(".",B2,FIND(".",B2)+1)+1)+1,LEN(B2)))  
=F2*256^3+G2*256^2+H2*256+I2

Yes, I used formulas instead of "Text to Columns" to automate the process of adding more information to a "living" worksheet.

The formulas in L2:P2 are the same, but replace B2 with C2.

The formulas in R2:V2 are also the same, but replace B2 with D2.

The formula for X2 is

=SUMPRODUCT(--($P$2:$P$8<=J2)*--($V$2:$V$8>=J2))

I also copied your original "valid" set in column A, which you'll see matches my result.

Upvotes: 3

Related Questions