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" )
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" )
Man, this is perfect, thank you soo much. I thought that Excel cannnot comprehend IP ranges that is why i have broken it down to octets so i can make calculations with the last octet (that is a range) but your XLOOKUP somehow just works. I owe you a beer.