Lookup IP addresses and map subnet against it

I have a file at work that has two sheets; "Subnet Info" & "Machine Info".

The Subnet Info sheet has the subnet details, like the subnet network address in one column (let's say A e.g. A2 = 10.10.0.0) & the mask in the other column (let's say B e.g. B = 16 where 16 means /16). Please see the image below.

Clipboard Picture (2022.03.02 11.32.22).png

The Machine Info sheet has the IP address of the servers in the environment along with some other information. What I want to achieve is that in the column (let's say D, marked in red in the picture), we look up the machine IP address and understand which IP range it falls under based on the subnet from the Subnet Info sheet and return that in Column D.

Clipboard Picture (2022.03.02 11.34.04).png

I know this is a forum to get help and not a job request, but I don't know where to start. Can this be done with Formula or VBA macro better? If yes can someone point me in the right direction? If someone has written something similar and can share that, it will be highly appreciated.

Please find the sample file attached.

P.S: I have data in the range of 200+ subnets and 2000+ in the machine info sheet.

If the IP could be looked up then nothing would be better. However, the subnet lookup is different. Let me elaborate.

10.10.0.0 /16 entails IP ranges 10.10.0.0 - 10.10.255.255 for e.g. 10.10.0.1, 10.10.0.2, 10.10.2.1, 10.10.2.2, 10.10.100.100, 10.10.200.200 all fall within this range. So we have to calculate and understand the range for each item in subnet info. Then understand the IPs that fall within this range.

10.10.100.100
10.10.1.5
10.10.20.30
10.10.120.121

All are part of 10.10.0.0 (/16) subnet.


10.160.32.150 - belongs to 10.160.32.0 /20 as the 10.160.32.0/20 covers ranges from 10.160.32.0 to 10.160.47.255
10.160.32.100 - belongs to 10.160.32.0 /20 it falls in the range mentioned above.

Originally Posted by Glenn Kennedy

Hehehe. Ok. I can drive a car... but I've no idea how the engine works. Same with Excel & computers. Out for an hour or so.

hahaha true that. I understood immediately that you are not aware of the IP subnetting but thank you so much for trying to help out. Appreciate it.

Bump. Anyone implemented something similar. Any & all help will be appreciated.