Search code examples
excellocationip-addressvlookupipv4

How to find location based on IP address in Excel

I've got a spreadsheet of about 5000 user events associated with IPs, and I'm trying to use IP to determine location using just an Excel formula. The IPs in my log are structured as "dotted quads" (decimal notation).

I figured VLOOKUP is the way to go here, so I downloaded WEBNet77's IPV4 IpToCountry database, which seems like a comprehensive and up-to-date resource (is there a better resource out there for this purpose?). The database includes about 140K rows of IP ranges, with the sheet structured like so:

IP address lookup

Colum labels A - G are, respectively: IP FROM, IP TO, REGISTRY, ASSIGNED, CTRY, CNTRY, COUNTRY.

Notice, a problem: columns A to B represent a range of IPs, but they are in the "long format" (I didn't realize this at first). So, before using these values as a reference, I'll have to convert my dotted quads. Then, I'd like to return Country (column G) for each of the IPs in my log.

Any help here?

And if you think there's a better way to lookup IP > Country in Excel (maybe using the web ie. http://api.hostip.info/flag.php?ip=xxx.xxx.xxx.xxx), please do let me know.

Solution

  • You can convert your quad IP to a numeric using this function 

    Function GetNumericIP(quadIP As String) As Long

    Dim sections
    On Error Resume Next
    sections = Split(quadIP, ".")

    GetNumericIP = sections(3) * 256 ^ 0 + sections(2) * 256 ^ 1 + sections(1) * 256 ^ 2 + sections(0) * 256 ^ 3
End Function

    And you can use MATCH/INDEX to get your location. The trick is to have your IP TO column sorted ascending. The Match function will return index of the row for the last value in the range which is less the given value.

    57.182.90.111  your input
968252015      =GetNumericIP(A1)
France         =INDEX(Country,MATCH(J6,IPTO,1)+1)
  (Or alternatively) =INDEX(Country,MATCH(J6,IPFROM,1))

    Notice I had to import the CSV (not just reference it externally) and I turned the columns into named ranges (Country, IPTO). For other's information, the CSV linked in the question has column names of:

    • IP FROM
    • IP TO
    • REGISTRY
    • ASSIGNED
    • CTRY
    • CNTRY
    • COUNTRY

    Alternately, you can convert your dotted quad IP into its constituent parts using only formulas, though it's a bit of a hassle. Here are the first two sections (my IP was in I5)

    =LEFT(I5,FIND(".",I5)-1)
=LEFT(RIGHT(I5,LEN(I5)-I10),SEARCH(".",RIGHT(I5,LEN(I5)-I10))-1)

    You can see...kind of a pain. Easier: use the VBA method explained above, OR use Text to Columns (on the ribbon: Data > Data Tools > Text To Columns > Delimited > Next > Other: "." > Next > Finish). Then write a formula to convert your split dotted-quad IP to the IP format in your reference database. This formula works:

    =sumproduct(J5:M5, 256^{3,2,1,0})

    Remember to sub in your column range for J5:M5 above. Then run MATCH/INDEX against the new value.