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

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.


Download Practice Workbook

Please download the workbook to practice yourself.


5 Suitable Procedures to Change Zip Code to State by Excel Formula

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.

Dataset of zip code to state excel formula


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

Using LOOKUP Formula to Change Zip Code to State by Excel Formula

  • Then, copy the following formula in the G5 cell.
=LOOKUP(2,1/($C$5:$C$10<=G4)/($B$5:$B$10>=G4),$D$5:$D$10)
  • Meanwhile, press the enter button.

Using LOOKUP Formula to Change Zip Code to State by Excel Formula

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.

Using LOOKUP Formula to Change Zip Code to State by Excel Formula

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


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

Inserting VLOOKUP Function to Change Zip Code to State by Excel Formula

  • Then, write down the following formula in the selected cell.
=VLOOKUP(G4,B5:D10,3,FALSE)

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 exact match

Inserting VLOOKUP Function to Change Zip Code to State by Excel Formula

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

Inserting VLOOKUP Function to Change Zip Code to State by Excel Formula

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


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

Changing Zip Code to State by Excel Formula by Introducing INDEX Function

  • Moreover, write down the following formula in the F5 cell.
=INDEX(B5:B10,MATCH(F4,C5:C10,0),1)

Here, the Match function looks up the value entered in 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 returned value of the Match Function.

Changing Zip Code to State by Excel Formula by Introducing INDEX Function

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

Changing Zip Code to State by Excel Formula by Introducing INDEX Function

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


Similar Readings


4. Using 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.

Using Geography Option in Excel 365 to Change Zip Code to State

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 be familiar with the method.

Steps:

  • Select the F5 cell first.

Changing Zip Code to State by Excel Formula by Introducing XLOOKUP Function

  • Then copy the following formula in the selected cell.
=XLOOKUP(F4,B5:B10,C5:C10)

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.

Changing Zip Code to State by Excel Formula by Introducing XLOOKUP Function

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

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. You will find such exciting blogs on our website Exceldemy.com. 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

Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo