Zip Code Lookup with COPILOT

By the end of this post, you’ll be able to enter a zip code into a cell and Excel will automatically populate the city and state cells! Specifically, we’ll use Excel’s WEBSERVICE and COPILOT functions. Think of this as combining the power of AI with Excel’s brilliant data connectivity.

Video

Overview of the Solution

This method uses:

• Excel’s WEBSERVICE function to call a free online API using a zip code
• Excel’s COPILOT function to extract the state abbreviation and city name from the API response
• Basic cell formatting tricks to handle zip codes with leading zeroes

By the end, we’ll have a dynamic and user-friendly lookup tool that updates the city and state details based on any zip code we type into a cell.

Step-by-Step Walkthrough

Step 1: Use WEBSERVICE to Fetch Zip Code Data

We’ll begin by calling a public API that returns location data based on a ZIP code. Let’s say we wanted to retrieve the data for zip code 90210. We would use the following URL (which we can enter into any web browser):

https://api.zippopotam.us/us/90210

It returns these results:

{"country": "United States", "country abbreviation": "US", "post code": "90210", "places": [{"place name": "Beverly Hills", "longitude": "-118.4065", "latitude": "34.0901", "state": "California", "state abbreviation": "CA"}]}

Now, to retrieve the results directly into Excel, rather than a web browser, we can use the WEBSERVICE function.

The WEBSERVICE function lets you pull data from a web URL directly into a cell. It’s useful for grabbing data from APIs or public web services without leaving your spreadsheet. It has a single argument, url, which is the full URL to the web service you want to call. It must be enclosed in quotes unless referencing a cell.

We could use this formula in a cell:

=WEBSERVICE("https://api.zippopotam.us/us/90210")

Or, if we stored the url in a cell, like B6, we could use this instead:

=WEBSERVICE(B6)

This will return a JSON response string that includes information such as the city, state, longitude, latitude, and country, directly into the cell:

Next up … let’s create a dedicated input cell for the zip code.

Step 2: Make the Zip Code Dynamic

Now we want the user to be able to type a ZIP code into a cell (say, A1) and have the data update automatically. We can adjust our formula to use the concatenation operator & to join the base url with the entered zip, like this:

=WEBSERVICE("https://api.zippopotam.us/us/" & A1)

Now, each time we type a zip code into A1, the lookup result updates accordingly.

Step 3: Extract City and State Information Using COPILOT

The JSON response includes everything we need. Let’s use the COPILOT function to extract the components we want.

The COPILOT function allows you to use natural language prompts to generate or transform data directly within Excel. It’s part of Excel’s AI-powered features and works by processing both a prompt and context to return intelligent responses.

=COPILOT(prompt, [context])

• prompt (required) – a natural language instruction telling Excel what to do. This is typically a string like “Get the state abbreviation” or “Extract the city name”.
• context (optional) – a reference to the data or cell that provides the input for the prompt. This allows Copilot to interpret and work with dynamic content.

Assuming the WEBSERVICE formula is in C8, we can extract the state abbreviation from the returned value with the following formula:

=COPILOT("get the state abbreviation", C8)

To extract the city (place name):

=COPILOT("get the place name", C8)

Excel Copilot intelligently reads the JSON and returns the city and state in a clean format. This is where it gets really fun!

Step 4: Combine It into a Single Formula

To reduce clutter, we can nest the WEBSERVICE call directly inside the COPILOT function. Here’s how we clean it up.

Assuming the zip code input cell is C8, we can use the following formula to return the state abbreviation:

=COPILOT("get the state abbreviation", WEBSERVICE("https://api.zippopotam.us/us/" & C8))

And for the city name:

=COPILOT("get the place name", WEBSERVICE("https://api.zippopotam.us/us/" & C8))

And the results:

Step 5: Handle Zip Codes with Leading Zeros

Some ZIP codes begin with a zero (e.g., 01101), and Excel by default removes leading zeros from numbers. To fix this, we can convert the input cell to text with these simple steps:

1. Select the zip code entry cell (C8 in our example).
2. Right-click and choose Format Cells…
3. Select Text as the data type and click OK.
4. Now enter your zip code (e.g., 01101), and the leading zero will be preserved.

Final Summary

We’ve used Excel’s WEBSERVICE function to bring external data into our workbook, and we’ve added a layer of intelligence with the COPILOT function, which extracts the information we need. Together, these tools allow us to create a zip code lookup tool that’s customizable and dynamic—without any manual copy-paste or complex JSON parsing.

If you’re excited about the potential for combining live data and AI within Excel, experimenting with tools like COPILOT and WEBSERVICE opens a lot of doors. Enjoy enhancing your spreadsheets!

Download Sample File

Frequently Asked Questions

1. What is the WEBSERVICE function in Excel?

It allows Excel to fetch data from a web URL and return it as text. It’s especially handy for APIs and real-time data retrieval.

2. What does COPILOT do in this example?

The COPILOT function reads and interprets the JSON returned by the web service, and extracts specific values like the state abbreviation or city name.

3. Can I use this method outside of the US?

Yes, the Zippopotam.us API supports several countries, but you need to change the country code in the URL (e.g., “CA” for Canada).

4. What happens if the user types in an invalid zip code?

The WEBSERVICE function may return an error or empty response, so consider adding error handling with =IFERROR().

5. Is COPILOT available to everyone?

The COPILOT function is only available to Microsoft 365 subscribers with appropriate licensing and updates applied. Always check your availability and permissions.

6. Will this work in Excel for Mac?

The WEBSERVICE function is not supported in all versions of Excel for Mac. Ensure you have a compatible version.

7. Can I extract other information like longitude or latitude?

Yes! Just adjust your COPILOT prompt to extract “longitude” or “latitude” from the same JSON result.

8. Why do I get #VALUE! errors sometimes?

This usually happens when the JSON is malformed or the internet connection times out. Always validate your source data.

9. Can I do batch lookups for multiple ZIP codes?

Currently, this setup works best for one ZIP code at a time, but you could expand it using helper columns and formulas to scale up.