Excel Formula to Change Zip Code to State (5 Examples)

In Excel, you may need to do a lot of work while working on a worksheet. One of the important works that can be done by Excel is to change a zip code to state by Excel formula. This is a handy and short process. In this article, I will show you the procedures to change zip code to state by using Excel formula. Hopefully, by following all the procedures step-by-step, you will increase your Excel skill.

Excel Formula to Change Zip Code to State: 5 Examples

Here, I am considering a dataset with the Maximum and Minimum Zip codes of the states with state names of the United States. The dataset has three columns, B, C, and D, called Max Zip Code, Min Zip Code, and State. The dataset ranges from B4 to D10 cells. There are another two cells where we would enter the zip code and Excel will return the state name.

1. Use LOOKUP Formula to Change Zip Code to State by Excel Formula

This is the first method of this article. Here, I will use the LOOKUP Function to change the zip code to state by Excel Formula. The process is short and handy, I hope you will understand the steps easily.

Steps:

• First, enter the zip code in the G4 cell.

• Then, copy the following formula in the G5 cell.

• Meanwhile, press the Enter button.

Here, the LOOKUP function looks for the value 2. The lookup vector is determined by applying some logical conditions. As we want to extract the State from the corresponding zip code, we set the range D5:D10 as [result vector].

• As a consequence, you will find the following result.

Read More: How to Create Zip Code with Excel Formula

2. Insert VLOOKUP Function to Change Zip Code to State by Excel Formula

This is the second method of this article. Here, I will use the VLOOKUP function to change the zip code to state by Excel formula. Please follow the following steps of this method. I have added the required illustrations for every step. You can easily understand the process by following the images.

Steps:

• First, select G5 cell after entering the zip code in G4.

• Then, write down the following formula in the selected cell.

Here, the VLOOKUP function looks up the value entered in the G4 cell in the range of B5 to D10 cell. 3 is the column number in the range containing the value to return. False means the return should be an exact match.

• After that, press the Enter.
• Consequently, you will find the result just like the picture given below.

Read More: How to Lookup ZIP Code in Excel

3. Change Zip Code to State by Excel Formula by Introducing INDEX Function

This is the third method of this article. In this method, I will show you the procedure of changing zip code to state by Excel formula by introducing the INDEX and MATCH functions. Here I have edited the dataset slightly. I will consider only one zip code for every state and the state names should be at the left side of the zip code. The step-by-step procedure of this method is given below.

Steps:

• First select the F5 cell after entering the zip code in the F4.

• Moreover, write down the following formula in the F5 cell.

Here, the MATCH function looks up the value entered in the F4 cell, looks for the value in the range of C5 to C10, and returns the exact match. Then, the INDEX function looks for the corresponding value in the range of B5 to B10 cells to get the match of the returned value of the MATCH Function.

• After pressing Enter, you will find the following answer.

4. Using the Geography Option in Excel 365 to Change Zip Code to State

This is the fourth method of this article. The process is quite long compared with the last three methods. But this method is also effective. I will not use any kind of formula hare. I will use the Geography option, a new feature of Excel 365 to change zip code to state. Here, I have changed the dataset slightly. I have a blank column named State with a filled column called State. The state name will appear in the state column. Follow the following procedures step-by-step.

Steps:

• Select the whole dataset first.
• Then, go to the Insert bar in your toolbar.
• After that, select the Table.

• Consequently, you will get a table form of the dataset just like the picture given below.

• Moreover, select the zip code.
• Additionally, select the data tab in your toolbar.
• Then, select the Geography option in the Highlighted.

• As a result, you will find the zip code column just like the picture given below.

• In the corner of the dataset, you will get an icon. Select it.

• Then, select Admin Division 1.

• As a consequence, you will find the result just like the picture given below.

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

5. Applying XLOOKUP Function

This is the last but not the least method of this article. Here, I will introduce another function called the XLOOKUP function to change zip code to state by Excel formula. This function is also a feature of Excel 365. I have changed the dataset slightly. Follow the following steps to become familiar with the method.

Steps:

• Select the F5 cell first.

• Then copy the following formula in the selected cell.

Here, the XLOOKUP function looks up the value entered in the F4 cell in the range of B5 to B10 cell. Then, returns the corresponding value found in the range of C5 to C10 for the value entered in F4.

• After pressing the Enter button, you will find the following result.

Things to Remember

• You should bear in mind that the last two methods can be done only in Excel 365.

Download Practice Workbook

Please download the workbook to practice yourself.

Conclusion

In this article, I have explained how to change zip code to state Excel formula. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. I hope you have enjoyed the whole tutorial. If you have any queries, please ask me in the comment section. Don’t forget to give us your feedback.

Related Articles

• How to Map Excel Data by ZIP Code
• How to Auto Populate Zip Codes in Excel

<< Go Back to Zip Code in Excel | Number Format | Learn Excel