The Scenario
You're a security analyst. The SIEM import is scheduled for tomorrow morning. Sitting in front of you: an Excel workbook with 800 unique IP addresses from last week's server access logs, one per row in column A. Each needs country, region, city, and ASN enrichment before the import can run.
The script that normally handles this enrichment was written by the contractor who left in January. The repo is on a server you can't access. You're starting from scratch on a Wednesday afternoon.
The bad version:
- Search for the IPinfo batch API docs, set up authentication with your API token, write a new script.
- Run the script, realize it's hitting the single-IP endpoint instead of the batch endpoint because you copied the wrong example.
- Switch to the batch endpoint, handle chunking at 1,000 IPs, parse the nested JSON, write the output to a CSV.
- Import the CSV back into Excel, align with the existing workbook columns, check that no rows slipped in the join.
Rebuilding data infrastructure under deadline pressure is a different kind of drain than the lookup itself. By the time the data is clean you're too tired to actually look at it.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook. It reads your IP list and, through its built-in IPinfo integration, calls the batch API and writes country, region, city, and ASN directly to your columns.
Open the SheetXAI sidebar and paste:
Look up all IP addresses in column A using IPinfo batch lookup and write country, region, city, and ASN to columns B through E. Flag any row where the country is outside the US, UK, or Canada as "Review" in column F.
What You Get
- Column B: two-letter country code for every row
- Column C: region or state name
- Column D: city
- Column E: ASN (e.g., "AS15169 Google LLC")
- Column F: "Review" for any IP outside the allowed countries, blank for passing rows
- Rows where IPinfo returns no data written as empty cells, not formula errors
What If the Data Is Not Quite Ready
IPs are mixed with other log fields in the same cell
The entries in column A contain timestamps and IPs in the format "2024-03-12T14:22:01Z 192.168.1.1". Extract the IP from each row, look up each IP with IPinfo batch lookup, and write country, region, city, and ASN to columns B through E.
Some rows contain private or reserved IP ranges
Look up all IPs in column A with IPinfo and write country, region, city, and ASN to columns B through E. Skip any row where the IP starts with 10., 172.16-31., or 192.168. and write "Private" in column B for those rows instead.
The same IP appears in multiple rows and should only be looked up once
Find all unique IP addresses in column A, look them up in bulk with IPinfo, write the results to a new worksheet called "IP Lookup Cache" with columns for IP, country, region, city, and ASN — then VLOOKUP back into the main worksheet to fill columns B through E for each row based on column A.
Full cleanup, enrichment, and flag in one operation
Column A has raw log entries mixing IPs, timestamps, and user agents separated by pipes. Extract the IP from each row, skip private ranges, deduplicate for the API call, look up country, region, city, and ASN via IPinfo batch lookup, write results to columns B through E using the cache, and mark "Review" in column F for any non-US/UK/CA IP or any row with no IPinfo data.
One instruction covers the cleanup and the enrichment together.
Try It
Get the 7-day free trial of SheetXAI and open any Excel workbook with a column of server access log IPs, then ask it to enrich and flag them with IPinfo. For related tasks see geographic traffic distribution or privacy flag screening. Hub overview: IPinfo + Excel.