Function Problems

How to Automatically Return a Region When Selecting a City in Excel

Anum Mushtaq Oct 14, 2026 869 views

Question details

The user wants to automatically display a specific region in an adjacent cell whenever a city is selected from a drop-down list.

How to Automatically Return a Region When Selecting a City in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing a dataset where roughly 250 cities are assigned to 40 regions, and the user needs to automate the region assignment upon selecting a city from a drop-down menu.
Observed behavior: The user currently has a working city drop-down list but needs the correct formula to look up and output the corresponding region in the cell next to it.

Before you start

Ensure you have a master reference table set up in your workbook (e.g., on a separate sheet named 'Cities') with cities in the first column and their corresponding regions in the second column.

Solution 1Recommended

Use the VLOOKUP Formula with IFERROR

This is the most standard and widely compatible method to find a matching region based on a selected city.

VLOOKUP searches for a specified value vertically down a column and returns a corresponding value from the same row. Wrapping it in IFERROR prevents ugly #N/A errors when the drop-down cell is empty.

Identify your reference table

Verify the location of your master list. For example, assume your reference cities are in sheet 'Cities' column A (A2:A250) and regions are in column B (B2:B250).

Select the target cell

Click on the cell next to your city drop-down menu where you want the region to automatically appear (e.g., cell E2, assuming the drop-down is in D2).

Enter the formula

Type the formula: =IFERROR(VLOOKUP(D2, Cities!$A$2:$B$250, 2, FALSE), "") and press Enter.

Apply to other rows

Click on the bottom-right corner of the cell containing the formula and drag the fill handle down to apply it to the rest of the column.

Absolute References: The dollar signs ($A$2:$B$250) lock the reference range so it won't shift when you copy the formula down.

Solution 2

Use the Modern XLOOKUP Formula (Microsoft 365 & Excel 2021)

XLOOKUP is a newer, more robust alternative to VLOOKUP that natively handles errors without needing the IFERROR wrapper.

WPS Spreadsheet Automation

Automate Data Entry Easily with WPS Spreadsheet

WPS Office fully supports advanced lookup functions like VLOOKUP and XLOOKUP, allowing you to seamlessly match cities to regions and automate your data entry tasks for free.

1. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Create the drop-down menu: Select the target cell, go to the Data tab on the ribbon, and click Data Validation to create your City list.
3. Apply the lookup formula: In the adjacent cell, enter =IFERROR(VLOOKUP(...)) or =XLOOKUP(...) just as you would in Microsoft Excel.
4. Save your work: Press Ctrl+S to save the document. WPS Spreadsheet natively uses the standard .xlsx format ensuring cross-platform compatibility.

100% compatible with Microsoft Excel formulas, functions, and formatting.Fully supports both VLOOKUP and the modern XLOOKUP function.Lightweight software that processes large datasets and formulas smoothly.Free to download with an intuitive, familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

The #N/A error occurs if the selected city in the drop-down does not match any entry in your master list exactly, which often happens due to hidden trailing spaces. Wrapping your formula in IFERROR will hide the error, but you should also check for typos or use the TRIM function to clean your data.

Can I return multiple columns based on the city selection?

Yes. If you use XLOOKUP, you can return an array of multiple columns simply by expanding the return array range (e.g., Cities!$B$2:$C$250). For VLOOKUP, you would need to write separate formulas in each adjacent cell, changing the column index number (e.g., 2, then 3).

How do I create the initial drop-down list for the cities?

Select the cell where you want the drop-down list. Go to the 'Data' tab on the ribbon, click 'Data Validation', choose 'List' under the Allow dropdown, and select your range of cities (e.g., Cities!$A$2:$A$250) as the source.

Excel & Data Problems