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.

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.

• Then, select Cell G5 and type this formula.

• 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.

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.

• Then, click on Cell C5 and inside this cell type this formula.

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

• Along with it, apply this formula in Cell D5 to get the County name as well.

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

Similar Readings

• How to Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)
• Create Zip Code with Excel Formula (6 Easy Ways)
• How to Map Excel Data by ZIP Code (2 Easy Methods)

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.

• Then, type this formula in Cell C5.

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

• Also, apply this formula to get the County name as well.

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

• How to Format Zip Code to 5 Digits in Excel (5 Easy Methods)
• Sort Data by Zip Code in Excel (With Easy Steps)
• How to Auto Populate Zip Codes in Excel (3 Easy Ways)
• Concatenate ZIP Codes in Excel (3 Easy Methods)