Function Problems

How to Automatically Return an Address Based on a City in Excel

Amos Gikunda Oct 9, 2026 869 views

Question details

The user needs a method to dynamically output a specific address based on a selected city name in Excel.

How to Automatically Return an Address Based on a City in Excel

Product: Excel
Device & OS: not provided
Scenario: Preparing a scalable data source for a Word mail merge where addresses need to automatically populate based on the city listed in the record.
Observed behavior: A dynamic formula is required to identify the city in a specific cell and automatically return the corresponding full address into another cell.

Before you start

Ensure your city data is standardized without accidental trailing spaces, and consider creating a separate reference sheet if you have a large list of cities and addresses.

Solution 1Recommended

Use XLOOKUP with a Mapping Table (Recommended)

This method is highly scalable and ideal for Word mail merges. A lookup table separates the data from the formula, making it easy to add or update addresses in the future.

Instead of hardcoding addresses into your formula, setting up a lookup table allows Excel to scan a specific range for the city and return the adjacent address.

Using IFERROR alongside XLOOKUP ensures that if a city is mistyped or missing, the cell remains blank instead of displaying an ugly #N/A error.

Create a Reference Table

On a new sheet or off to the side, create a two-column table. List your cities in the first column (e.g., 'City') and the full addresses in the second column (e.g., 'Address'). Select the data and press Ctrl+T to format it as a Table named 'LookupTable'.

Enter the XLOOKUP Formula

Click on the cell where you want the matching address to appear (e.g., cell Z1). Type the formula: =IFERROR(XLOOKUP(A1,LookupTable[City],LookupTable[Address]),"")

Apply and Drag

Press Enter to apply the formula. If the city in cell A1 matches a city in your reference table, the address will populate. Drag the fill handle at the bottom-right of the cell downwards to apply this formula to your entire list.

Use XLOOKUP with a Mapping Table (Recommended)

Mail Merge Ready: Once your address column is fully populated using XLOOKUP, you can easily use this column as your address block data field in your Word mail merge.

Solution 2

Use Nested IF Statements for Short Static Lists

If you only need to return addresses for two or three cities, a nested IF statement is a quick solution that requires no additional reference tables.

Handle Data Efficiently

Automate Data Lookups Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like XLOOKUP, IFERROR, and nested IFs out of the box. It offers a familiar interface, making it incredibly easy to manage address lists and prepare data for seamless mail merges.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet containing the city names, and set up your lookup table.
2. Apply Lookup Formulas: Use the exact same =XLOOKUP or =IF formulas you would use in Excel to retrieve your addresses automatically.
3. Merge in WPS Writer: Save your file as an .xlsx, open WPS Writer, go to the 'Mailings' tab, and select your spreadsheet as the data source to finish your mail merge.

100% compatible with Microsoft Excel formulas like IF, VLOOKUP, and XLOOKUP.Seamlessly integrates with WPS Writer for efficient, hassle-free mail merges.Free, lightweight, and capable of handling large datasets without lagging.Familiar tabbed interface so you can start working immediately without a learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

What if my XLOOKUP formula returns an error when a city is missing?

You can wrap your XLOOKUP function in an IFERROR function. By using =IFERROR(XLOOKUP(...), ""), Excel will output a blank cell instead of an #N/A error whenever a city is not found in the lookup table.

Can I use VLOOKUP instead of XLOOKUP to return addresses?

Yes, VLOOKUP works perfectly for this task. However, you must ensure that your reference table has the 'City' in the first (leftmost) column and the 'Address' in a column to the right. The formula would look like =VLOOKUP(A1, LookupTableRange, 2, FALSE).

Why does my nested IF formula say 'You've entered too many arguments'?

This usually happens if a comma or quotation mark is misplaced inside the formula. Check that each IF statement contains exactly three arguments: the condition, the value if true, and the value if false (which is often the next IF statement). Ensure all text strings are enclosed in double quotes.

How do I link these calculated addresses to a Word document?

Save your Excel file containing the filled address column. Open Microsoft Word or WPS Writer, navigate to the Mailings tab, click 'Select Recipients', choose 'Use an Existing List', and open your saved spreadsheet. You can then insert the 'Address' merge field into your document.

Excel & Data Problems