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.
=CustomerThen, 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.
Read More: How to Find ZIP Code from Address in Excel
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.
Read More: How to Convert Zip Code to State with VLOOKUP in Excel
Similar Readings
- How to Format Zip Code to 5 Digits in Excel
- Sort Data by Zip Code in Excel
- How to Concatenate ZIP Codes in Excel
- Create Zip Code with Excel Formula
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
Download Practice Workbook
You can download and practice from this workbook.
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.
