How to Match IP Addresses to Network Subnet Ranges in Excel

Managing large-scale IT inventories often leads to a frustrating bottleneck: accurately mapping individual IP addresses to their correct network subnet ranges in Excel. While traditional IPAM tools provide this utility, access limits often force network administrators to rely on tedious manual verification.

Implementing a specialized array formula grants you the ability to automate this correlation directly within your worksheets. Under the stipulation that IP addresses must first be converted to decimal format to resolve CIDR notation, this method is highly efficient. For example, matching 192.168.1.55 to the 192.168.1.0/24 range becomes instantaneous. Below, we outline the exact formula configuration and logic to implement this solution.

Managing network logs, asset inventories, or security audits often requires mapping individual IP addresses to their corresponding network subnet ranges. Since Excel treats IP addresses as standard text strings rather than numerical sequences, performing direct comparisons (such as checking if an IP is "between" a start and end range) is not natively supported by basic functions.

To solve this, we must translate these IP addresses and subnets into a format that Excel can mathematically evaluate. The most efficient way to achieve this is by converting IPv4 addresses into 32-bit decimal integers. Once converted, matching an IP to a subnet becomes a straightforward numerical boundary check. This guide will walk you through the math, the formulas for both legacy and modern Excel, and how to build a dynamic IP-to-subnet lookup engine.

The Underlying Logic: IP to Decimal Conversion

An IPv4 address consists of four 8-bit octets separated by periods (e.g., 192.168.1.15). Each octet represents a value from 0 to 255. Mathematically, we can convert an IP address string into a single decimal number using the following formula:

Decimal Value = (Octet1 * 256³) + (Octet2 * 256²) + (Octet3 * 256¹) + (Octet4 * 256⁰)

Or simplified:

Decimal Value = (Octet1 * 16777216) + (Octet2 * 65536) + (Octet3 * 256) + Octet4

By converting both your individual IP addresses and your subnet boundaries (Start IP and End IP) into these decimal values, you can instantly determine if an IP falls within a subnet using simple logical operators: IP_Decimal >= Subnet_Start_Decimal AND IP_Decimal <= Subnet_End_Decimal.

Step 1: Convert an IP Address to Decimal in Excel

Depending on your version of Excel, you can use either a highly compatible legacy formula or a streamlined Excel 365 formula.

Method A: The Classic Excel Formula (Universal Compatibility)

If you are using Excel 2019, 2016, or older, you must parse the string using text manipulation functions. The most reliable classic formula leverages SUBSTITUTE and MID to extract each octet:

=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",100)),{1,101,201,301},100))*256^{3,2,1,0})

How it works:

• SUBSTITUTE(A2,".",REPT(" ",100)) replaces every period in the IP address with 100 spaces, effectively isolating each octet within its own block.
• MID(..., {1,101,201,301}, 100) extracts 100-character-wide slices starting at character positions 1, 101, 201, and 301. This ensures we capture each isolated octet.
• TRIM strips away the excess space characters, leaving us with an array of four text-based numbers: {"192", "168", "1", "15"}.
• We multiply this array by 256^{3,2,1,0} (which evaluates to {16777216, 65536, 256, 1}).
• SUMPRODUCT sums the multiplied values to output the final 32-bit decimal equivalent: 3232235791.

Method B: The Modern Excel Formula (Excel 365 & 2021)

If you are using Excel 365 or Excel Web, you can take advantage of the TEXTSPLIT function, which makes the conversion formula significantly shorter and easier to read:

=SUM(TEXTSPLIT(A2,".")*256^{3,2,1,0})

This achieves the exact same result by splitting the string at each period and multiplying the resulting array by the corresponding exponents of 256.

Step 2: Parse CIDR Subnets into Start and End Decimals

Subnets are typically represented in Classless Inter-Domain Routing (CIDR) notation, such as 192.168.1.0/24. To match an IP to this subnet, we need to extract the base IP, find the subnet mask, and calculate the decimal range boundaries.

1. Extracting the Base IP and Mask Prefix

If your CIDR block is in cell B2 (e.g., 192.168.1.0/24):

• Base IP Address: =LEFT(B2, FIND("/", B2)-1)
• CIDR Prefix (Mask): =VALUE(MID(B2, FIND("/", B2)+1, 2))

2. Calculating the Subnet Start and End Decimal Boundaries

Once you have isolated the components, you can calculate the decimal limits for the range. Set up your subnet reference table with the following column formulas:

Subnet Start Decimal (Column C):
This is simply the decimal value of the Base IP. Assuming the Base IP is in cell C2:

=SUM(TEXTSPLIT(C2,".")*256^{3,2,1,0})

Subnet End Decimal (Column D):
The size of a subnet range is calculated as 2^{(32 - prefix)}. The last address in the subnet is the start decimal plus the range size minus 1. Assuming the Start Decimal is in D2 and the CIDR prefix is in E2:

=D2 + (2^(32 - E2) - 1)

For example, with 192.168.1.0/24:

• Start IP: 192.168.1.0 → Decimal: 3232235776
• Prefix: 24
• End Decimal Formula: 3232235776 + (2^(32-24) - 1) → 3232235776 + 255 → 3232236031 (which is 192.168.1.255)

Step 3: Matching IPs to the Subnet Ranges

With both your target IPs and your subnet reference tables converted to decimals, you can use lookup formulas to map the target IP to its corresponding subnet.

Example Table Configurations

Let's assume you have two sheets or tables: Subnets Reference Table and IP Audit Table.

Subnets Reference Table (Range: G2:I10)

IP Audit Table

Assuming you want to lookup the target IP in cell A2:

Using SUMPRODUCT (For exact, non-sorted arrays)

If your reference tables are small or not sorted, SUMPRODUCT can scan the boundaries and pull the correct network name:

=INDEX(I$2:I$10, SUMPRODUCT((Target_IP_Decimal >= G$2:G$10) * (Target_IP_Decimal <= H$2:H$10) * ROW(I$2:I$10)) - ROW(I$2) + 1)

Where Target_IP_Decimal is the calculated decimal value of the IP you are looking up.

Using XLOOKUP (Highly optimized for Excel 365)

If your Subnets Reference Table is sorted in ascending order by the Start Decimal column, you can use XLOOKUP with an approximate match mode. This is computationally much faster for large datasets of thousands of IPs.

=XLOOKUP(Target_IP_Decimal, G$2:G$10, I$2:I$10, "No Match Found", -1)

Note on exact validation: The -1 match mode matches the next smaller item (which represents the start of the subnet range). To ensure the target IP hasn't breached the corresponding End Decimal, wrap the formula inside an logical check:

=LET(
    target, SUM(TEXTSPLIT(A2,".")*256^{3,2,1,0}),
    matched_start, XLOOKUP(target, G$2:G$10, G$2:G$10, 0, -1),
    matched_end, XLOOKUP(target, G$2:G$10, H$2:H$10, 0, -1),
    network_name, XLOOKUP(target, G$2:G$10, I$2:I$10, "Unknown Network", -1),
    IF(AND(target >= matched_start, target <= matched_end), network_name, "IP Out of Defined Ranges")
)

Modern Clean Setup: The Complete Single-Cell LAMBDA

If you are working with Excel 365 and want to avoid using multiple helper columns in your master IP tracking lists, you can consolidate the parsing and math steps into a single formula. Define this formula using the LET function to keep your worksheet neat:

=LET(
    ip, A2,
    subnets, G$2:G$10,
    starts, H$2:H$10,
    ends, I$2:I$10,
    names, J$2:J$10,
    ip_dec, SUM(TEXTSPLIT(ip, ".") * 256^{3,2,1,0}),
    match_row, MATCH(1, (ip_dec >= starts) * (ip_dec <= ends), 0),
    INDEX(names, match_row)
)

Performance Tip for Large Inventories

While dynamic array formulas like LET, TEXTSPLIT, and SUMPRODUCT are incredibly convenient, calculating hundreds of thousands of IP decimal operations on the fly can cause Excel workbook lag. For datasets with more than 50,000 rows:

• Convert the formula calculations to static values once computed by copying the column and using Paste Values.
• Sort your subnet reference table by Start Decimal to enable Excel to leverage its binary search algorithm under XLOOKUP, which is hundreds of times faster than scanning unsorted arrays using SUMPRODUCT.