How to find location based on IP address in Excel

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.