<div>
Thought you could utilise two functions.<br />
<br />
Created two worksheets. One with subnet list and other with the server list.<br />
<br />
Functions one. To get a potion of sub string in a subset set. I used a helper column on the server list which even when you hide still works fine.<br />
<br />
=IFERROR(LEFT(A2,LEN(A2)-F<wbr />IND(&quot;.&quot;,A2<wbr />)),) &nbsp;-to help identify position of last dot in the subnet defined. Check excel for understanding. Let me know in case of any query<br />
<br />
Function two. Used the Vlookup to utilise results of function one.<br />
<br />
=VLOOKUP(C3&amp;&quot;*&quot;,SUBNETS!$A<wbr />$1:$B$15,2<wbr />,FALSE) -With sub string of a subnet got with the help column defined, we can easily use vlookup to get the exact Subnet name as per your definition.<br />
<br />
I wont explain much on but i would like you to refer to the attached excel and rate accordingly.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/12_w50/1214658/sev_subnet.xlsx" target="_blank" class="file-inline" title="Sub_ip (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>sev_subnet.xlsx</a>
</div>


sev_subnet.xlsx

<div>
That won't work on anything other than a 24bit subnet mask. Have a look at the macro in this post for solution for all masks<br />
<a href="https://www.experts-exchange.com/questions/28993108/Vlookup-nonexact-IP-address-match.html" rel="ugc">https://www.experts-exchange.com/questions/28993108/Vlookup-nonexact-IP-address-match.html</a>
</div>


<div>
Shaun, sorry to disagree with you. It works. Download the excel attached and test.<br />
This functions created don't have any consideration on the mask but the strictly string structure of any class of IP.
</div>


<div>
And 172.3.2.100 is not in 172.3.2.1/28 and your sheet says it is.<br />
That subnet is only 172.3.2.1 - 172.3.2.14<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/12_w50/1214764/subnets.png" target="_blank" title="subnets.png (25 KB)"><img alt="subnets.png" class="file-block lazyload" style="max-width: 553px;" data-src="https://filedb.experts-exchange.com/incoming/2017/12_w50/1214764/subnets.png" /></a>
</div>


subnets.png

<div>
That is a sample data on the excel sheet.<br />
I m working on a basis of this question, which is excel worksheet. <br />
Your network calculation of course is correct but we are asked for reference which I believe is as simple as the excel.
</div>


<div>
Then see the link I posted which is a simple Excel Macro that works out subnet masks correctly.<br />
And if I may, your sheet included the sample data with a 28bit mask which prompted my post<br />
<br />
See attached<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/12_w50/1214765/TestIP.xlsm" target="_blank" class="file-inline" title="TestIP.xlsm (70 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>TestIP.xlsm</a>
</div>


TestIP.xlsm

<div>
<div class="content wysiwyg-content">
Hi guys.<br />
<br />
I have a worksheet with a list of subnets in a CIDR format, example 192.168.0.15/24.<br />
On another worksheet I have a list of servers, along with their respective IP address, say 192.168.0.10.<br />
<br />
What kind of formula would allow me to compare the value of each server's IP address and match it with the relevant subnet ?<br />
<br />
Thanks for any help.
</div>
</div>


Hi guys.

I have a worksheet with a list of subnets in a CIDR format, example 192.168.0.15/24.
On another worksheet I have a list of servers, along with their respective IP address, say 192.168.0.10.

What kind of formula would allow me to compare the value of each server's IP address and match it with the relevant subnet ?

Thanks for any help.

Excel - function to find subnet match

Networking is the process of connecting computing devices, peripherals and terminals together through a system that uses wiring, cabling or radio waves that enable their users to communicate, share information and interact over distances. Often associated are issues regarding operating systems, hardware and equipment, cloud and virtual networking, protocols, architecture, storage and management.

Networking

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.