Musiha Mahfuza Mukta
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.
How to Lookup ZIP Code in Excel: 4 Suitable Methods
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.
As a result, you will see the following output.
- 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.
As a result, you will get all the cities and countries for the given ZIP codes.
Read More: How to Find ZIP Code from Address 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
- 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.
Read More: How to Create Zip Code with Excel Formula
Similar Readings
- How to Format Zip Code to 5 Digits in Excel
- [Fixed] Zip Codes in Excel Starting with 0
- How to Lookup County from Zip Code in Excel
- Sort Data by Zip Code in Excel
- How to Auto Populate Zip Codes in Excel
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.
- 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.
- 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.
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.
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:Â Find ZIP Code from Address in Excel
💬 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.
- 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.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 4 suitable methods to lookup a ZIP Code in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
