match string / find number between 2 numbers

gn82

New Member
Joined
Aug 6, 2011
Messages
22
Hi,

I am trying to match a list of IPs and Applications to their security zomes. I thought I could cover half the problem (primary IPs only) if I transform all IPs into numbers, which I did. Sadly I do not know how to find a number (IP) between the subnet upper and lower limits.


ABCD
FGHIJ
1Application namePrimary IP AddressPrimary IP into NumberSecondary IP
Security zoneIP Range startIP Range endRange start into numberRange end into number
2Application 110.100.1.1510,100,001,015

Internal-restricted10.100.1.010.100.1.6310,100,001,00010,100,001,063
3Application 210.100.1.7310,100,001,073

Internal-voip10.100.1.6410.100.1.12710,100,001,06410,100,001,127
4Application 3192.168.20.55192,168,020,055

DMZ192.168.20.0192.168.20.255192,168,020,000192,168,020,255
5Application 483.1.25.1083,001,025,01083.1.25.97, 83.1.25.103, 83.1.25.209
Internet-Zone 1109.11.93.192109.11.92.255109,011,093,192109,011,092,255
6Application 5109.11.93.195109,011,093,195

Internet-Zone 283.1.25.083.1.25.25583,001,025,00083,001,025,255

<tbody>
</tbody>

Desired table

Application nameIP AddressSecurity zone
Application 110.100.1.15Internal-restricted

<tbody>
</tbody>


Thanks in advance !!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Just go with something like
Code:
if(and(c2 > i2 , c2 < j2),f2,"")
<j2),f2,"")[ code]<j2),f2,"")[="" quote]<j2),f2,"")"<j2),f2,"")<="" html=""></j2),f2,"")[>
 
Upvote 0
Just go with something like
Code:
if(and(c2 > i2 , c2 < j2),f2,"")
<j2),f2,"")[ code]<j2),f2,"")[="" quote]<j2),f2,"")"<j2),f2,"")<="" html=""></j2),f2,"")[>

Hi Nils,

the A-D columns and the F-J columns do not have a direct 1-2 match.
I need a formula that takes each application IP, then goes through the F-J columns and when it finds a match in the ranges then it returns the name of the zone.

Your formula assumes there is a per-line relationship between the A-D and the F-J columns. It is not. If it helps consider them different tabs.

Thanks in advance
 
Upvote 0
Just go with something like
Code:
if(and(c2 > i2 , c2 < j2),f2,"")
<j2),f2,"")[ code]<j2),f2,"")[="" quote]<j2),f2,"")"<j2),f2,"")<="" html=""></j2),f2,"")[>

I think it might work with a INDEX(MATCH) formula that somehow integrates a IF(AND) to ensure the 2 upper/lower limits are checked against, but that is beyond my level of excel magic :)
 
Upvote 0
No you could do this step for every security zone ->
Code:
= if(above for zone 1) & if(above for zone 2) & if(...)

but that would require a fixed amount of security zones and there could be multiple matches, too.

i'll give it a thought when i have more time
 
Upvote 0
No you could do this step for every security zone ->
Code:
= if(above for zone 1) & if(above for zone 2) & if(...)

but that would require a fixed amount of security zones and there could be multiple matches, too.

i'll give it a thought when i have more time

The only problem there is that there are about 2'000 IP Sublets (ranges) and 18'000 aplications, which makes it impossible to go through the list manually or to add an aditional IF for each subnet. That is why I am trying to figure out if there is some way to autoamte this awful process.

For a while I though of making a looooong list with all the IPs imaginible, going through the application list (probably vlookup / match) to find where I have a IP string match (primary or secondary IP) and save it. Then turn all those IPs into numbers and find a way to identify which fits into what subnet and then grab the name of the zone for that subnet.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top