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

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

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.


Download Practice Workbook

You can download and practice from this workbook.


3 Simple Ways to Auto Populate Zip Codes in Excel

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.

Data set - auto populate zip codes in excel

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.

selecting for Drop-down list

Step 2:

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

populate using VLOOKUP

Step 3:

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

=Customer

Then, press OK.

finding Drop-Down list

The Drop-Down box will look like this.

drop-down list

Step 4:

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

=VLOOKUP($H$4,B5:E12,2,FALSE)

Populate Excel with VLOOKUP

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)

auto populating Zip Code with Excel VLOOKUP

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.

Read More: Excel Formula to Change Zip Code to State (5 Examples)


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)),"")

Populating with INDEX-MATCH in excel

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)),"")

populate Zip Code in excel with INDEX-MATCH

Note: these three equations differ only in lookup_value. You can use it as it suits you.

Read More: How to Convert Zip Code to State with VLOOKUP in Excel


Similar Readings


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),"")

Populating with HLOOKUP

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),"")

populating Zip Code using HLOOKUP

Note: In these formulas, we used different row_index_num i.e. 2,3,4.

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


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

Sourav Kundu

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

Leave a reply

ExcelDemy
Logo