Forum Discussion

AttilaHetey's avatar
AttilaHetey
Copper Contributor
Oct 16, 2023
Solved

Excel formula help

Hey,  I have the following table.    A B C D E F G H I J K L M N IP Address CIDR Netmask Start IP End IP Broadcast IP # of Hosts 1st OCT 2st OCT 3st OCT IP Start  IP ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 16, 2023

    AttilaHetey 

    On Excel 365, and if I understood the logic correctly, that could be

    =XLOOKUP(
 1,
 (TEXTBEFORE($A12,".",3) = TEXTBEFORE($D$3:$D$8,".",3)) *
 (--TEXTAFTER($A12,".",3) >= --TEXTAFTER($D$3:$D$8,".",3)) *
 (--TEXTAFTER($A12,".",3) <= --TEXTAFTER($E$3:$E$8,".",3)),
 $N$3:$N$8,
 "no such" )

SergeiBaklan's avatar
SergeiBaklan
MVP
Oct 19, 2023

AttilaHetey 

Sorry, my logic was wrong, assumed first 3 octets are the same.

Taking into account that you already have Start IP and End IP formula could be

=LET(
startIP,   $D$3:$D$9, endIP, $E$3:$E$9, type, $N$3:$N$9, ip, $A12,
decimallP, LAMBDA(v, SUM(TEXTSPLIT(v, ".")*256^{3,2,1,0}) ),
afterStart,BYROW( startIP, LAMBDA(v, decimallP(ip) >= decimallP(v)  ) ),
beforeEnd, BYROW( endIP, LAMBDA(v, decimallP(ip) <= decimallP(v) ) ),
XLOOKUP(1, afterStart*beforeEnd, type,"no such")
)

In it every IP address is converted into decimal form and we compare them not care about octets.

Didn't play with that, in general we may include into above formula calculation of Start/End IP as well using CIDR.

AttilaHetey's avatar
AttilaHetey
Copper Contributor
Oct 19, 2023
Thanks a lot man, this now works like a charm. I tested it and i think its fine now. Mind that i have over 7k IP ranges and will probably increase.
Thanks again

Resources