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:
Thanks a lot
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
- How to import values from excel with pandas into tkinter faster?
- Speed up concatenation of excel files with Pandas
- Trying to send screenshot of excel in an email
- Sum of text Does not contain in Range
- Stopping Excel Macro executution when pressing Esc won't work
- Consolidated Date window
- Excel formula for calculating difference between two date columns
- Get number of decimals in python float, including trailing zeros
- Excel VBA search for data and stuck at a certain point due to group or split source file?
- Convert Time Input in Decimal Format - Excel Formula or VBA
- Lookup unique "Values" if they follow a certain "Pattern" from a lookup table then return a "Name"
- Share office scripts connected to Excel with external organisations
- Tiling an Excel array in 2 dimensions, ideally not using VBA
- How to get the cartesian product of two sets via Excel Formulas
- Repeat Value for Every Instance of Another Value in Excel without using Power Query
- VSTACK doesn't pile up dynamic ranges
- How To Close a UserForm Properly in Excel VBA?
- In Excel, can I use a hyperlink to run vba macro?
- EXCEL: Count all unique values in Table column up to a certain point, and combine them into a single string?
- Excel VBA connection to SQL Server with Microsoft Entra MFA authentication
- Excel shows incorrect polynomial trendline function
- Split too large VBA into multiple Subs
- Trying to calculate user whos spent the most and the least - Excel
- How to do a union of two columns and then sort them into a third column in Excel?
- Is there a way to create a simple custom formula in Excel without VBA?
- Copying a String Array into a Range of cells (in MS Excel using VBA)
- vlookup with formula calculated from lookup cell location
- How to extract R InstallPath from Registry using Excel VBA
- How to inner-join in Excel (eg. using VLOOKUP)
- Scientific notation when importing from Excel in .Net