Search code examples
excelexcel-formulaip

Excel - Formula to see if IP address is contained within list of subnets

I am trying to create a formula, where i compare a list of IP addresses with a list of subnets. Let's say IP addresses (hosts) in column A and Subnets in Column B, with a CIDR format (e.g. 192.168.1.0/24)

How do I compare each IP to the list of subnets, so that the formula returns "TRUE" if the subnet contains at least one IP in column A, and "FALSE" if the subnet doesn't contain any IP?

My objective, trivially, is to find out if such subnets have active hosts (Column A) in them or not.

I found an Excel macro which contians many functions for manipulation of IP addresses (link removed for security purposes), however there don't seem to be any functions in it which are useful to my cause.

Below is an image,as an example of what I'm trying to achieve, a check on the subnet is made for each IP address, in order to verify if the subnet is used by at least one host or not: Example

Thanks a lot

Solution

  • Personally, I would take a loot at this github.

    A ton of answers about VBA IP Functions.

    https://github.com/andreafortuna/VBAIPFunctions/blob/master/IPFunctions.vba