How to Return a State from a ZIP Code Using XLOOKUP or VLOOKUP
Question details
The user needs to automatically fill in a state name when entering a ZIP code by matching it against a master list stored on a different worksheet.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating data entry across multiple worksheets by looking up ZIP codes in a master reference table and returning the corresponding states.
- Observed behavior
- The user seeks the correct function syntax and layout to ensure the ZIP code entered on the active sheet correctly pulls the state data from the master sheet.
Ensure you have a master worksheet prepared with your reference data, containing all ZIP codes in one column and their corresponding states in another column, and that both columns are formatted consistently (e.g., both as Text) to prevent matching errors.
Use XLOOKUP to Match and Return the State
XLOOKUP is the modern, highly flexible lookup function that searches a range or array and returns an item corresponding to the first match it finds, without requiring a specific column order.
XLOOKUP is generally preferred over VLOOKUP because it allows you to look up values regardless of whether the return column is to the left or right of the search column. It also handles exact matches by default.
Click on the cell in your current worksheet where you want the state name to appear.
Type =XLOOKUP(A2, 'MasterList'!A:A, 'MasterList'!B:B, "Not Found"), assuming A2 is the cell where you type the ZIP code, 'MasterList'!A:A is the column containing master ZIP codes, and 'MasterList'!B:B is the column with state names.
Press Enter. The state corresponding to the ZIP code will now appear. You can drag the fill handle down to apply this formula to other cells in the column.
Use VLOOKUP for Older Software Compatibility
If you are using an older version of spreadsheet software that does not support XLOOKUP, VLOOKUP is the standard alternative for vertical data matching.
Easily Match and Lookup Data Across Sheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced data lookup functions, including both VLOOKUP and the modern XLOOKUP. Managing master data tables and automating your cross-sheet data entry is completely seamless.
- 1. Open your Workbooks: Launch WPS Spreadsheet and open the file containing your input sheet and your master ZIP code list.
- 2. Insert the Function: Navigate to the Formulas tab, click on 'Lookup and Reference', and select either XLOOKUP or VLOOKUP from the dropdown.
- 3. Select your Arguments: Follow the on-screen formula dialogue box to select your lookup value, lookup array, and return array across your different sheets.
- 4. Apply and Automate: Click OK to generate the result. Double-click the bottom-right corner of the cell to instantly apply the lookup formula to your entire dataset.
Frequently Asked Questions
Why is my VLOOKUP returning an #N/A error for valid ZIP codes?
This usually occurs due to a data type mismatch. For example, the ZIP code on your input sheet might be formatted as a Number, while the ZIP code in your master list is formatted as Text. Ensure both columns use the exact same format.
How do I prevent leading zeros in ZIP codes from disappearing?
Spreadsheets often treat numbers with leading zeros (like 07030) as regular numbers and drop the zero. Select your ZIP code columns, right-click, choose 'Format Cells', and set the category to 'Text' or 'Special -> Zip Code' before entering your data.
Can I use XLOOKUP if the state column is to the left of the ZIP code column?
Yes. Unlike VLOOKUP, which requires the search value to be in the leftmost column, XLOOKUP can search in any direction. You simply select the ZIP code column as the lookup array and the state column as the return array, regardless of their visual order.
How do I reference a master list in a completely different workbook file?
You can reference an external workbook by having both files open. When typing your XLOOKUP or VLOOKUP formula, click over to the other workbook to select your ranges. The formula will automatically generate the external file path reference.
