Forum Discussion
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 ...
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" )
There is something wrong though.
If i search 10.11.185.184
It cannot find this in the range of
IP CIDR Netmask Start IP End IP
10.11.185.0 16 255.255.0.0 10.11.0.1 10.11.255.254
Do you know why this is the case? i see this with every 16 CIDR while it should be within the range.
If i search 10.11.185.184
It cannot find this in the range of
IP CIDR Netmask Start IP End IP
10.11.185.0 16 255.255.0.0 10.11.0.1 10.11.255.254
Do you know why this is the case? i see this with every 16 CIDR while it should be within the range.
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.
- 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 againAttilaHetey , you are welcome, glad to help