Hi everyone,

I'm trying to accomplish the following:

Have a some logic around checking an IP address against say 30 subnet ranges. We'll say Cell F2 has 10.1.1.1, I need to build something to check if that cell contains something in this range:

> 10.1.0.0 AND <10.1.255.255, "Insert Subnet Name", if not
is it > 10.2.0.0 AND <10.2.255.255, "Insert Subnet Name", if not
is it > 10.3.0.0 AND <10.3.255.255, "Insert Subnet Name", if not
Etc for another 30 subnets.

I've made it work for one (obviously pretty easy), but when I had to nest them together it got complex and out of my depth. I have roughly 7000 cells I have to apply this to, and will just be filling the formal down the entire sheet for F3, F4, F5 etc.

Any help would be very appreciated.

TJ

2. ## Re: Nested If revolving around IP addresses & subnet masks

You could create a table of IP Address ranges. In reality, you only need the first two octets so you can extract them and use a VLOOKUP against a table.

Have a look at the example workbook to see how this might work.

Regards, TMS

3. ## Re: Nested If revolving around IP addresses & subnet masks

I would use an index/match against a table, and as only the first 2 parts are actually needed, it makes it pretty simple
see attached

Hope this helps

EDIT-
Darn, beat again...lol

4. ## Re: Nested If revolving around IP addresses & subnet masks

Darn, beat again...lol
Way beat ...

But very similar solutions ... and same comment about the structure.

Regards, TMS

5. ## Re: Nested If revolving around IP addresses & subnet masks

Thanks TM, got caught on the phone and forgot to refresh before posting...lol

6. ## Re: Nested If revolving around IP addresses & subnet masks

This is exactly what I was looking for. I've adapted it and it works like a charm. Thanks for taking the time to help, it's appreciated.

7. ## Re: Nested If revolving around IP addresses & subnet masks

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)