Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Find IP address in multiple range and return user group

How to find the zone using a specific IP address in a range between two IP addresses

In this tutorial I will show you how we can for a specific IP address to find the user (security zones) between two IP address or range of IP addresses. In the picture below you see the situation.

The image below in the table we have a list of IP addresses Start / End. In the column 'B' have defined the range of IP address belongs to a certain group of IP security (security zones). The table below Excel application user enters a specific IP address.

Our goal is to create a formula that would automatically for a specific IP address (IP address) to return a security group (security zones). The results are in cells C11.....C20.

IP address in range

Find the IP address range, and returns users to a specific group range between two IP addresses

The first way of solving the problem is the use of additional columns that can hide that they are not visible and do not bother us. In the picture below you will see that I am adding three columns.


The first column 'F' contains the IP address in column 'C' which have been translated from the "dot format" in the classic text/number.
The second column 'G' contains the IP address in column 'D' that are converted from "dot format" in the classic text/number.
The third column contains a copy of the column 'B' group names users (security zones). This layout and converting the data we need to be able to use the VLOOKUP formula, see below.

Find the IP address in the range of IP addresses and return the user group for a specific range of IP addresses

The solution task using VLOOKUP formula

Pay attention to your operating system or Excel applications using comma (,) instead of a semicolon (;) in formulas.
The formula in cell 'F3' converts the IP address (the numbers with dots) of the "dot format" in the text, and then again converted to a number. Since there are four groups of numbers between the points, that can be one-digit, two-digit and three-digit, automatically formats the formulas in each group of three digits. The formula is as follows and should be in a single line of code. Copy a formula to the last row.

=VALUE(TEXT(LEFT(C3;FIND(".";C3)-1);"000")&TEXT(MID(C3;FIND(".";C3)+1;FIND(".";C3;FIND(".";C3)+1)-1-FIND(".";C3));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3)+1)+1;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)-1-FIND(".";C3;FIND(".";C3)+1));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)+1;LEN(C3));"000"))

The formula in cell 'G3' converts IP address (the numbers with dots) from "dot format" in the text, and then again converted to a number. Since there are four groups of numbers between dots, that can be one-digit, two-digit and three-digit, automatically formats the formulas in each group of three digits. The formula is as follows and should be in a single line of code. Copy a formula to the last row.

=VALUE(TEXT(LEFT(D3;FIND(".";D3)-1);"000")&TEXT(MID(D3;FIND(".";D3)+1;FIND(".";D3;FIND(".";D3)+1)-1-FIND(".";D3));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3)+1)+1;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)-1-FIND(".";D3;FIND(".";D3)+1));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)+1;LEN(D3));"000"))

The formula in cell 'H3' copies the data from cell B3. Copy a formula to the last row.

=B3

The formula in cell 'C11' search condition from the cell 'B11' in the range F3:F7 and returns the equivalent data from column 'H' of the same row (column 'H' is the number 3). If the formula does not find the condition of the cells 'B11' then search the first lower value and returns again equivalent data in the same row in which finds the respective lower value. This under VLOOKUP formula does not use the last argument, which is the key to finding the smallest number. But if you want, you can set the last argument to TRUE (behind the number 3 set ";true" argument).

NOTE! You notice that in the above table IP addresses arranged in ascending order (A - Z) per column 'C'. Given that the last argument of the VLOOKUP function is omitted (or can be true) then the data are required to be arranged in ascending order (A - Z). The formula must be in a single line of code. Copy a formula down to the last row.

=VLOOKUP(VALUE(TEXT(LEFT(B11;FIND(".";B11)-1);"000")&TEXT(MID(B11;FIND(".";B11)+1;FIND(".";B11;FIND(".";B11)+1)-1-FIND(".";B11));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11)+1)+1;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)-1-FIND(".";B11;FIND(".";B11)+1));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)+1;LEN(B11));"000"));$F$3:$H$7;3)

Solve the problem in by using the INDEX/MATCH formulas

The figure below see the similar situation as in the previous example. In this case, I added two extra column and use the formula from the previous example. The other way to solve a problem I've made using INDEX / MATCH functions, which are in fact better performance and a replacement for the VLOOKUP function. The task is the same. To condition the IP address of the cell 'B11' needs as a result of the return to a group of users for a specific range of cells. NOTE! You notice in the picture below, the IP addresses are not sorted.

Find the IP address in the range of IP addresses, which determines the range and return users belongs to a group of IP addresses
The first column 'F' contains the IP address from column 'C' which have been translated from the "dot format" in the classic text/number.
The second column 'G' contains the IP address from column 'D' that are converted from "dot format" in the classic text/number.

The formula in cell 'F3' is as follows. Copy a formula to the last row.

=VALUE(TEXT(LEFT(C3;FIND(".";C3)-1);"000")&TEXT(MID(C3;FIND(".";C3)+1;FIND(".";C3;FIND(".";C3)+1)-1-FIND(".";C3));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3)+1)+1;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)-1-FIND(".";C3;FIND(".";C3)+1));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)+1;LEN(C3));"000"))

The formula in cell G3 is as follows. Copy a formula to the last row.

=VALUE(TEXT(LEFT(D3;FIND(".";D3)-1);"000")&TEXT(MID(D3;FIND(".";D3)+1;FIND(".";D3;FIND(".";D3)+1)-1-FIND(".";D3));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3)+1)+1;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)-1-FIND(".";D3;FIND(".";D3)+1));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)+1;LEN(D3));"000"))

The formula in cell 'C11' search condition from the cells 'B11' in range 'F3:F7' and returns the equivalent data from column 'B' (B3:B7) from the same row. After copying in your Excel application set the formula in a single line.

=INDEX($B$3:$B$7; SUMPRODUCT(--(VALUE(TEXT(LEFT(B11;FIND(".";B11)-1);"000")&TEXT(MID(B11;FIND(".";B11)+1;FIND(".";B11;FIND(".";B11)+1)-1-FIND(".";B11));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11)+1)+1;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)-1-FIND(".";B11;FIND(".";B11)+1));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)+1;LEN(B11));"000"))<=$G$3:$G$7); --(VALUE(TEXT(LEFT(B11;FIND(".";B11)-1);"000")&TEXT(MID(B11;FIND(".";B11)+1;FIND(".";B11;FIND(".";B11)+1)-1-FIND(".";B11));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11)+1)+1;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)-1-FIND(".";B11;FIND(".";B11)+1));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)+1;LEN(B11));"000"))>=$F$3:$F$7);ROW($A$1:$A$5)))

So, how to find IP address from multiple IP range and return user group? I hope you realize that this task can be solved in two ways. There is another way, and that I am not talking about the versions using VBA or UDF. This is enough from me, this is blog for Excel beginners.