+ Reply to Thread
Results 1 to 5 of 5

Formula To Search IP Range for Location

  1. #1
    Registered User
    Join Date
    09-01-2012
    Location
    atlanta
    MS-Off Ver
    Excel 2003
    Posts
    3

    Formula To Search IP Range for Location

    So I have attached a screen shot, of a basic example of what i'm trying to accomplish.

    Simply put, If want to have a certain cell "Device IP" as its listed in my table, have a certain IP address entered in to it. I want the IP in that cell to be looked up to see if it matches a range.

    IE 192.168.1.0-192.168.1.254, which I have in Cell A2 "192.168.1.0" and in Cell A3 I have "192.168.1.254". In Cell A4 I have a location. If the IP that was entered matches in that range the location will show up in the "Location" tab.

    Please let me know if you need any further clarification.

    Using Excel 2010 x86.

    excel help.png

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Formula To Search IP Range for Location

    hhmmmmmm, i think you have same problem with same sample with this one...

    http://www.excelforum.com/excel-form...turn-text.html
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    09-01-2012
    Location
    atlanta
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Formula To Search IP Range for Location

    yea i have looked at that, its very similar to that. If i have all my IPs listed in Column A then it seems to work. I however don't want to list out a 255 ip's for the 200+ subnets that I have in one column. also, the only that works is if the location is listed next to every ip in a range in column B. THat doesn't seem to be like a real time saver or practical solution.

    ---------- Post added at 11:40 PM ---------- Previous post was at 11:38 PM ----------

    my actual VLookUP that I am using is below:
    =VLOOKUP([@[Device IP]],IPS!$A1:$IV1,257,FALSE)

    THe issue is that I have all 255 Ips from A1-IV1 for 1 site. and the site location in IW1. this lookup returns N/A for every single ip that I input into my lookup cell.

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Formula To Search IP Range for Location

    maybe you should post a dummy workbook for that.

  5. #5
    Registered User
    Join Date
    09-01-2012
    Location
    atlanta
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Formula To Search IP Range for Location

    Attached is a dummy workbook that has the information on what im trying to do.

    Dummy Workbook.xlsm

    Dummy Workbook.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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