# How to Auto Populate Zip Codes in Excel (3 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

Managing data can be very easy if we can populate Excel cells automatically. This article will show you how we can auto populate Zip Codes in Excel.

## How to Auto Populate Zip Codes in Excel: 3 Simple Ways

There are some convenient ways to extract Records on criteria from a large dataset. Some of the easy ways are shown in this article.

### 1. Auto Populate Zip Codes Using VLOOKUP Function

First, let us have a dataset. This dataset contains 4 columns. Using the VLOOKUP function, we will input cells from column Serial Number and populate in the other 3 columns i.e. Name, Zip Code, and State. Step 1:

First, we have to create a Drop-Down list.

For the Drop-Down list, select all the cells in Serial Number. Then, select the Name Box and give a suitable name. We named it Customer. Step 2:

Now, copy all the data from the Serial Number column and select Data Validation from the Data tab. Step 3:

You have to allow the List option and write the below formula in the Source box.

`=Customer`

Then, press OK. The Drop-Down box will look like this. Step 4:

After that, write the following formula in the H5 cell and press ENTER.

`=VLOOKUP(\$H\$4,B5:E12,2,FALSE)` Thus, we will get Luke in H5 who is attributed to serial number 1.

For Zip Code,

`=VLOOKUP(\$H\$4,B5:E12,3,FALSE)`

For State, use the following formula.

`=VLOOKUP(\$H\$4,B5:E12,4,FALSE)` Thus, Zip Codes and other fields are auto populated in Excel.

Note: in these formulas for the VLOOKUP function, we have used different column_index_numbers i.e. 2,3,4.

### 2. INDEX and MATCH Functions to Auto Populate Zip Codes

In this section, the combination of INDEX and MATCH functions has been used. The INDEX function here uses the MATCH function as its argument.

First, create a Drop-Down box as shown previously. Then follow the following procedure.

Step 1:

Write the following formula in H5 and press ENTER.

`=IFERROR(INDEX(\$C\$5:\$C\$12,MATCH(\$H\$4,\$B\$5:\$B\$12,0)),"")` Step 2:

To get Zip Code, write the following formula in H6 and press ENTER.

`=IFERROR(INDEX(\$D\$5:\$D\$12,MATCH(\$H\$4,\$B\$5:\$B\$12,0)),"")`

To know which State the person belonged to, write the following formula in H7 and press ENTER.

`=IFERROR(INDEX(\$E\$5:\$E\$12,MATCH(\$H\$4,\$B\$5:\$B\$12,0)),"")` Note: these three equations differ only in lookup_value. You can use it as it suits you.

### 3. HLOOKUP to Auto Populate Zip Code

The HLOOKUP function is used to find and retrieve data from horizontal tables.

That’s why, to apply this method, we have to change the Dataset horizontally.

Step 1:

First, align your  Dataset horizontally and create a Drop-Down list in E9.

Then, write the following formula in E10 and press ENTER to get the Name attributed to the serial number.

`=IFERROR(HLOOKUP(\$E\$9,\$C\$4:\$J\$7,2,0),"")` Step 2:

For Zip Code, write the following formula in E11 and press ENTER.

`=IFERROR(HLOOKUP(\$E\$9,\$C\$4:\$J\$7,3,0),"")`

And, to find out the State the person belongs to, use the following formula and press ENTER.

`=IFERROR(HLOOKUP(\$E\$9,\$C\$4:\$J\$7,4,0),"")` Note: In these formulas, we used different row_index_num i.e. 2,3,4.

Read More: How to Lookup ZIP Code in Excel

## Conclusion

That’s all for the article. We’ve tried to show you ways to auto populate zip codes in Excel. If you have any queries, please do leave a comment. For other Excel articles do check our site.

## Related Articles Sourav Kundu

Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  