+ Reply to Thread
Results 1 to 11 of 11

Need to find a IP address from within IP ranges and return the range name

  1. #1
    Registered User
    Join Date
    03-02-2019
    Location
    Nottingham, England
    MS-Off Ver
    Excel 365
    Posts
    2

    Need to find a IP address from within IP ranges and return the range name

    Hi,

    Hoping someone can help, I have found similar questions on here but none exactly the same and the existing ones don't seem to match what I need to do.

    I have a spreadsheet with over 1000 IP ranges listed on it and another sheet with thousands of IP addresses. I need to find the range name and subnet for the IP address from the list of ranges.

    Some of my ranges are split so I cannot just focus on the first 3 parts of the IP.

    Example:

    IPrange.JPG


    I then have a list of addresses and need to find them in the ranges and return the Subnet, VLAN name and Status against that address


    10.194.23.10 and I need to find all the above info.
    10.194.24.245

    so I need to lookup the IP between A2 & B7 and return the values for Collumn C, D & E etc.


    I tried a few examples on here but cannot think how I can do it when I have so many ranges to cater for (around 1000 different ranges).

    Can anyone help
    thanks in advance

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,809

    Re: Need to find a IP address from within IP ranges and return the range name

    Hi, welcome to the forum

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Need to find a IP address from within IP ranges and return the range name

    The formatting of IP addresses conflicts with the basic logic used in a greater than / less than formula which is what you would need to compare a value between others, making this a little more challenging than you would think that it should be.

    In the attached workbook, I have used the following formula in F2:I7 to break down the Start Range addresses into individual octets, in J2:M7 to break down the End Range the same, and finally in F12:!3 to break down the individual IP addresses that we are going to be comparing to the table.

    =VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",LEN($A2))),(COLUMNS($F2:F2)-1)*LEN($A2)+1,LEN($A2))))

    In N2:N7 I have added a simple formula to make indexing the results more efficient. Please note that the method that I have used will not work if any lookup value returns more than one correct result, for example if you have any duplicated or overlapping Start or End ranges.

    =ROW()

    Finally, this formula in D12:D13 to compare the 4 octets of the individual IP address to the broken down Start and End ranges, then grab the row number from N2:N7 and turn it into a useful result.

    =IFERROR(INDEX(C$1:C$7,1/(1/SUMIFS($N$2:$N$7,$F$2:$F$7,"<="&$F12,$G$2:$G$7,"<="&$G12,$H$2:$H$7,"<="&$H12,$I$2:$I$7,"<="&$I12,$J$2:$J$7,">="&$F12,$K$2:$K$7,">="&$G12,$L$2:$L$7,">="&$H12,$M$2:$M$7,">="&$I12))),"")

    This will trap any IP addresses that don't fall within the specified ranges and return blank cells.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-02-2019
    Location
    Nottingham, England
    MS-Off Ver
    Excel 365
    Posts
    2

    Re: Need to find a IP address from within IP ranges and return the range name

    Thank you so much Jason.b75

    That works a charm and has saved me at least a days work if not more. I have substituted the ranges with names for the ranges so that I can add to them as new IP ranges come online.

    thanks again

  5. #5
    Registered User
    Join Date
    10-03-2020
    Location
    Sevilla
    MS-Off Ver
    365
    Posts
    1

    Re: Need to find a IP address from within IP ranges and return the range name

    Hello,

    There is a problem with this.

    Example:

    Range: 10.20.30.40 - 10.40.40.40

    Looking for IP: 10.40.10.40

    If u use any value lower doest not work.
    What we can do?

    Thanks

  6. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Need to find a IP address from within IP ranges and return the range name

    Formula without additional columns
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,809

    Re: Need to find a IP address from within IP ranges and return the range name

    Quote Originally Posted by Josejk View Post
    Hello,

    There is a problem with this.

    Example:

    Range: 10.20.30.40 - 10.40.40.40

    Looking for IP: 10.40.10.40

    If u use any value lower doest not work.
    What we can do?

    Thanks
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  8. #8
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Need to find a IP address from within IP ranges and return the range name

    @FDibbins I'm not agree with you. The previous solution have a mistake.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,809

    Re: Need to find a IP address from within IP ranges and return the range name

    BMV, Josejk posted hos question on another member's thread, that is not permitted - whether an answer given here is correct or not.

    Josejk needs to start their own thread, and reference this 1 if they feel it necessary.

  10. #10
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Need to find a IP address from within IP ranges and return the range name

    @FDibbins -Ok. On the Russian resource we try to keep question in one thread if the subject is the same or very close to each other. Sorry.

  11. #11
    Registered User
    Join Date
    01-06-2021
    Location
    china
    MS-Off Ver
    2016
    Posts
    1

    Re: Need to find a IP address from within IP ranges and return the range name

    how about returning value as a boolean? do you have sample formula?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find address and return GPS location from another sheet
    By shelbygt500 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2014, 04:35 AM
  2. [SOLVED] Find a value in a range then return the cell address
    By corinereyes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-16-2014, 06:48 AM
  3. Find IP address in subnet range and return text
    By jpalmer99 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-22-2013, 04:32 PM
  4. [SOLVED] Return value if within range, return multiple values if ranges overlap
    By cde1983 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2013, 06:16 AM
  5. [SOLVED] Use address of named range to find same address in another worksheet
    By dwsteyl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2013, 04:56 PM
  6. Find variable - Return cell address
    By r.coon in forum Excel General
    Replies: 7
    Last Post: 03-26-2011, 04:07 AM
  7. Find, return string address in a range of cells with numbers and text
    By Vera22 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-09-2007, 08:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1