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" )

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