Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Lookup ZIP Code in Excel (4 Suitable Methods)

If you are searching for how to look up a ZIP code for the name of a country or city in Excel, then you have come to the right place. Today, in this article I will explain how to lookup a ZIP code in Excel.

Actually, Excel has a wonderful feature named Data Type. With geographic data type, you can easily find the location of any valid ZIP code.


Download Practice Workbook

You can download the practice workbook from here:


4 Methods to Lookup ZIP Code in Excel

Here, I will describe 4 suitable methods to lookup a ZIP code in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Which contains some ZIP codes.


1. Applying Geographic Data Type to Lookup ZIP Code

Here, I will use the Geography tab to find the City and Country for the given ZIP codes. Basically, for the looking up, I will include geographical data from Excel. Now, let’s talk about how you can insert geographical data for different ZIP codes in Excel.

Steps:

  • Firstly, select the ZIP codes.
  • Secondly, from the Data tab >> click on the Geography feature, which is under Data Types. Furthermore, you might use the drop-down arrow to find the Geography Data Type.

Applying Geographic Data Type for ZIP Code in Excel

As a result, you will see the following output.

Excel Geographic data using ZIP Code

  • Now, click on the symbol beside the ZIP codes.

So, you will get all the geographic information including the detailed location related to that ZIP code.

  • After that, select the B5 cell.
  • Then, click on the Field icon.
  • Then, choose City.

As a result, you will see the city name holding that ZIP code in a cell next to B5 means in the C5 cell.

  • After that, select the C5 cell.
  • Then, click on the Field icon.
  • Then, choose County/region.

So, you will see the country name holding that ZIP code in a cell next to C5 means in the D5 cell. Finally, you have both the city and country name of that ZIP code.

  • Then, to get the other geographical locations select C5 & D5 cells and then use the Fill Handle icon.

Use of Fill Handle icon for other ZIP Codes in Excel

As a result, you will get all the cities and countries for the given ZIP codes.

Read More: How to Lookup County from Zip Code in Excel


2. Using Formula Bar in Excel

Here, you can follow another way to insert the geographical location for that particular ZIP code. Actually, I’m talking about the use of Formula Bar. So, let’s see the steps.

Steps:

  • First, in the C5 cell write down (=B5.) then you will be available to choose any type of available information from a list.

As I want to insert the city name, so I have chosen City. Thus, the formula becomes.

=B5.City

Using Formula Bar in Excel to Lookup ZIP Code

  • Subsequently, press ENTER.

At this time, you will see the city name for that ZIP code.

  • Similarly, for the country name, the formula becomes:
=C5.[Country/region]
  • Consequently, press ENTER.

As a result, you will see the country name for that ZIP code.

  • Then, to get the other geographical locations select C5 & D5 cells and then use the Fill Handle icon.

Finally, you will get all the cities and countries for the given ZIP codes.

Using Geographic Data Type to Lookup ZIP Code in Excel

Read More: How to Create Zip Code with Excel Formula (6 Easy Ways)


Similar Readings


3. Use of VLOOKUP Function to Lookup Zip Code

You can use the VLOOKUP function to look up a ZIP code for the name of a country or city in Excel. For this, at first, you have to remove the geographic sign from the ZIP codes. So, follow the steps below.

Steps:

  • Firstly, copy the previous ZIP codes along with the name of cities and countries.
  • Secondly, go to another worksheet and select a cell.
  • Thirdly, from the Home tab >> choose Paste Values which is under Paste options.

Use of VLOOKUP Function to Lookup Zip Code in Excel

  • Now, select the data.
  • Then, right-click on the Mouse.
  • After that, from the Context Menu Bar >> go to Data Type >> choose Convert to Text.

So, you will see the following dataset.

  • Now, you have to select a cell, where you want to keep the result. Here, I have selected the G8 cell.
  • Then, you need to use the corresponding formula in the G8 cell.
=VLOOKUP(G7,B5:D18,2,FALSE)
  • Then, press ENTER.

Formula Breakdown

  • lookup_value = G7: The value that it looks for in the leftmost column of the given table.
  • table_array = B5:D18: The table in which it looks for the lookup_value in the leftmost column.
  • col_index_num = 2: The number of that particular column in the table from which a value is to be returned.
  • [range_lookup] = FALSE: 0 or False for an exact match, 1 or True for a partial match. So, it denotes the exact match for the lookup_value

  • Similarly, write another formula in the G9 cell to find the country name.
=VLOOKUP(G7,B6:D19,3,FALSE)

As here index column is 3 so it will search for the country name.

  • Then, press ENTER.

Result of using VLOOKUP function to Lookup ZIP Code in Excel

Read More: How to Convert Zip Code to State with VLOOKUP in Excel


4. Employing LOOKUP Function in Excel

You can use the LOOKUP function to look up a ZIP code for the name of a country or city in Excel. Let’s see the steps.

Steps:

  • Firstly, follow method-3 to remove the geographic sign from the ZIP codes.
  • Secondly, you have to select a cell, where you want to keep the result. Here, I have selected the G8 cell.
  • Then, you need to use the corresponding formula in the G8 cell.
=LOOKUP(2,1/($B$5:$B$18<=$G$7),$C$5:$C$18)
  • Then, press ENTER.

Employing LOOKUP Function for ZIP Code in Excel

Formula Breakdown

  • Here, ($B$5:$B$18<=$G$7)—> will do a logical test, and where the G7 value is within $B$5:$B$18 array then it will return TRUE.
    • Output: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
  • Then, we divided 1 by the previous array. So, all the answers will be errors except the TRUE value. Where it will return 1.
  • So, 2 is the lookup value and {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!} is the lookup vector.
  • $C$5:$C$18 is the resultant vector.
  • Lastly, the LOOKUP will return the city name from the C column.

  • similarly, write another formula in the G9 cell to find the country name.
=LOOKUP(2,1/($B$5:$B$18<=$G$7),$D$5:$D$18)

As here resultant vector is $D$5:$D$18 so it will search for the country name.

  • Then, press ENTER.

Read More: Excel Formula to Change Zip Code to State (5 Examples)


💬 Things to Remember

  • While using the VLOOKUP and LOOUP functions you have to paste the values properly.
  • Furthermore, you can only find those ZIP codes which you insert in the dataset.
  • Below, I have attached an image where the result shows an error. Actually, the ZIP codes in the dataset have extra space just before their codes. That’s why the VLOOKUP function can’t find that similar value from the array. Even though the ZIP code was there.

Things to Remember about looking up ZIP code in Excel

  • So, when I remove the space then the function returns the answer.

  • So, you should be careful about it. Or, in the case of a long dataset, you can use extra space within the lookup value.
  • Otherwise, you can insert the ZIP codes and related cities and countries manually.
  • However, the best option is to use geographic data type to find that particular location using ZIP code.

Practice Section

Now, you can practice the explained method by yourself.

Practical Section to look up ZIP code in Excel


Conclusion

I hope you found this article helpful. Here, I have explained 4 suitable methods to lookup a ZIP Code in Excel. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo