Forum Discussion

AttilaHetey's avatar
AttilaHetey
Copper Contributor
Oct 16, 2023

Excel formula help

Hey, 

I have the following table. 

 

ABCDEFGHIJKLMN
IP AddressCIDRNetmaskStart IPEnd IPBroadcast IP# of Hosts1st OCT2st OCT3st OCTIP Start IP EndCountryType
       12344  
10.16.215.024255.255.255.010.16.215.110.16.215.25410.16.215.25525410162151254HUServer
10.17.215.024255.255.255.010.17.215.110.17.215.25410.17.215.25525410172151254ATImage Device
10.10.170.2328255.255.255.24010.10.170.1710.10.170.3010.10.170.311410101701730SIIOT
10.11.1.024255.255.255.010.11.1.110.11.1.25410.11.1.255254101111254CROExternal Managed Devices
10.10.32.027255.255.255.22410.10.32.110.10.32.3010.10.32.3130101032130HUMaxnet card readers
10.10.32.6426255.255.255.19210.10.32.6510.10.32.12610.10.32.1276210103265126HUWhatever

 

These are IP adressess (in A) and i have already broken them down into ranges - column K and L 

In a seperate field i want to compare the values in this table column H-L with an IP address (4 seperate columns e.g. 10.16.215.2 to 4 columns 10 16 215 2) and find the exact row where the IP addrss is matching.

In other words - I want to create a search field where someone puts an IP address - it is then converted into 4 columns for the octets (i know how to do that) and then compared/matched with the 4 numbers in the table from column H-L by also making sure that the 4th searched number is within the range of K-L. I want to return Column M and N as a result. 

My goal is a searchable database where one can put in an IP and immadietely sees the country and the type/description without manually going through the list. 

 

I have tried 

=IF(AND(U15>=$K3, U15<=L3), IF(COUNTIF(H2:H6,R15)+COUNTIF(I2:I6,S15)+COUNTIF(J2:J6,T15)>=3, "Match", "Not Match"), "Not Match")

 

But this is not really the solution as it does not search in the whole column and it just returns Match or NO Match. Also tried with Vlookup but i am lost at this point.

 

Any help is appriciated, i am not great with functions in general unfortunately. 

 

Thanks a lot

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

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 16, 2023

    AttilaHetey 

    Will the CIDR never be less than 24? It it were less than 24, the first three octets of the end IP could be different from that of the start IP.

    • AttilaHetey's avatar
      AttilaHetey
      Copper Contributor
      Oct 17, 2023
      Sergei have answered with a working code, but thank you also for looking into this mate.
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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 17, 2023
      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.
    • AttilaHetey's avatar
      AttilaHetey
      Copper Contributor
      Oct 17, 2023
      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.
      • 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.

Share

Resources