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

How to Convert Zip Code to State with VLOOKUP in Excel

Zip codes are defined as the postal code of certain cities or states of a country. It is used as a part of the address. Every country has its database of zip codes and its relevant state or city names. But sometimes we may find some database where we only get the zip codes. In this case, we can use the VLOOKUP function in excel to find out the state name holding that specific zip code. In this article, we will go through 3 quick methods to convert zip code to state with VLOOKUP.


Download Practice Workbook

Download this sample file and try the process by yourself.


Introduction to VLOOKUP Function

Before beginning the processes, let us know the VLOOKUP function in a brief. The VLOOKUP function is used to look up certain data in a bunch of data strings that are organized vertically. It comprises approximate and exact matching. Along with it, we use Wildcard Characters (*, ?) for partial matches. The lookup value is fetched from the first column of the original dataset.

Syntax

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

Arguments

  • lookup_value: The first column value to look for in the table.
  • table_array: Original data table to fetch the value.
  • column_index_num: The column number in the table to get the value.

Optional Argument

range_lookup: TRUE (approximate match) and FALSE (exact match).


3 Quick Methods to Convert Zip Code to State with VLOOKUP in Excel

To describe the methods, here is a list of Zip Codes along with their State and County names in the Cell range B5:D19.

VLOOKUP Zip Code to State


In general, the length of this type of database is huge. Therefore, we need to use the following 3 methods to find a specific State from the Zip code.

1. Apply VLOOKUP Function for Changing Zip Code to State for Single Worksheet

In this first method, we will use the VLOOKUP function to get the State from Zip Code in between a single worksheet.

  • First, type the Zip Code number in Cell G4.

Apply VLOOKUP Function for Changing Zip Code to State for Single Worksheet

  • Then, select Cell G5 and type this formula.
=VLOOKUP(G4,B5:C19,2,FALSE)

VLOOKUP Zip Code to State

  • After this, press Enter.
  • That’s it, you will get the State from Zip Code.
  • You can compare the final output from the original dataset.

In this formula, we applied the VLOOKUP function to find the lookup_value of Cell G4 from the table_ array B5:C19. Along with it, 2 is given as column_index_num and FALSE for an exact match.

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


2. Use VLOOKUP Function to Convert Zip Code to State for Two Worksheets

We can also use the VLOOKUP function between two worksheets to convert any Zip Code to State. In this case, we will apply the formula in one worksheet using the values from another worksheet. Let’s see how it works.

  • In the beginning, type your required Zip Code in Cell B5.

Use VLOOKUP Function to Convert Zip Code to State for Two Worksheets

  • Then, click on Cell C5 and inside this cell type this formula.
=VLOOKUP(B5,Dataset!B5:C19,2,FALSE)

Use VLOOKUP Function to Convert Zip Code to State for Two Worksheets

  • Afterward, press Enter and you will get the State name.

Here, we used the VLOOKUP function to find the lookup_value of Cell B5. As the original dataset is located in another worksheet, we provided the worksheet name with the cell range as the table_array. Along with it, 2 is given as column_index_num and FALSE for an exact match.
  • Along with it, apply this formula in Cell D5 to get the County name as well.
=VLOOKUP(B5,Dataset!B5:D19,3,FALSE)

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


Similar Readings


3. Combine VLOOKUP & IF Functions to Get State from Zip Code

Here is another solution to get the State name from Zip Code by fusing the VLOOKUP and IF functions. To do the task, follow the process below.

  • First, insert the Zip Code number in Cell B5.

Combine VLOOKUP & IF Functions to Get State from Zip Code

  • Then, type this formula in Cell C5.
=IF(B5="","",VLOOKUP(B5,Dataset!$B$5:$C$19,2))

Combine VLOOKUP & IF Functions to Get State from Zip Code

  • Following, press Enter and you will get the State based on the Zip Code.

The IF function is used to get a logical comparison based on the value of Cell B5. Following, we used the Quotation Marks (“ “) to avoid blank cells. Also, use the Cell range B5:C19 as an absolute reference in the VLOOKUP function.
  • Also, apply this formula to get the County name as well.
=IF(B5="","",VLOOKUP(B5,Dataset!$B$5:$D$19,3))

Read More: How to Format Zip Codes in Excel (2 Easy Methods)


Things to Remember

  • You can insert column references instead of specific cell ranges in the VLOOKUP function. It will also give an accurate value.
  • Make sure to provide the correct column-index-num to avoid any errors.
  • If you need to find more information along with State names like City, Area Code, etc. you can use the cell range as an absolute cell reference to get all values at once.

Conclusion

Henceforth we are at the end of our article. Here, we guided you on how to convert Zip Code to State with VLOOKUP in excel with 3 quick methods. Let us know your feedback on this tutorial. Also, keep an eye on ExcelDemy for more articles like this.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo