Function Problems

How to Auto-Populate an Excel Cell from a Drop-Down Selection

John Wilson Oct 14, 2026 870 views

Question details

The user needs to automatically populate a cell with specific text based on a selection made in a drop-down list located in another cell.

How to Auto-Populate an Excel Cell from a Drop-Down Selection

Product: Excel
Device & OS: not provided
Scenario: Automating data entry by linking a drop-down list selection to a corresponding value in an adjacent column.
Observed behavior: The goal is to have Excel return a corresponding text in column C when an item is selected from a drop-down list in column B, such as returning 'Blue' for 'Amy'.

Before you start

Ensure you have created a reference table with your drop-down items in one column and their corresponding return values in the adjacent column before writing your formula.

Solution 1Recommended

Use the XLOOKUP Function

Use XLOOKUP to easily search for the selected drop-down item and return its corresponding value. This is the most modern and flexible approach.

XLOOKUP allows you to search for a value in one column and return a corresponding value from another. It replaces older functions like VLOOKUP and provides more robust matching.

Create a reference table

Set up a two-column lookup table anywhere in your workbook. For example, place names in column E (E2:E5) and their corresponding colors in column F (F2:F5).

Select the target cell

Click on cell C2, where you want the corresponding text to appear after making a selection in the drop-down list in cell B2.

Enter the XLOOKUP formula

Type the formula =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,"") into the formula bar and press Enter.

Fill the formula down

Click the small square at the bottom-right corner of cell C2 and drag it down to apply the formula to the rest of the cells in column C.

Absolute References: Using dollar signs ($) in the formula creates absolute references, locking your lookup table range in place so it doesn't shift when you copy the formula down to other rows.

Solution 2

Use the VLOOKUP Function

For older versions of Excel that do not support XLOOKUP, VLOOKUP is a reliable alternative to fetch data from a reference table.

Work seamlessly with Excel files

Easily Create Drop-Down Lists and Lookup Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data validation and lookup functions like VLOOKUP and XLOOKUP, allowing you to easily automate cell population based on drop-down selections in a familiar interface.

1. Open your workbook: Launch WPS Spreadsheet and open your existing file or create a new workbook.
2. Create a drop-down list: Go to the Data tab, click 'Data Validation', choose 'List' under Settings, and select the range containing your items.
3. Apply lookup formulas: Use =XLOOKUP() or =VLOOKUP() exactly as you would in Microsoft Excel to populate adjacent cells based on your drop-down selections.

Fully compatible with Microsoft Excel formats (.xlsx)Supports advanced formulas like VLOOKUP and XLOOKUPBuilt-in Data Validation tool for easy drop-down list creationFree and lightweight alternative for seamless data management

microsoft office alternative - wps office

Frequently Asked Questions

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

An #N/A error usually means the item selected in your drop-down list does not exactly match any item in your lookup table. Check for accidental trailing spaces, spelling differences, or ensure your lookup range is locked with absolute references ($).

Can I place the lookup table on a different worksheet?

Yes. When typing your formula, simply navigate to the other sheet and select your lookup range. The sheet name will automatically be added to your formula reference (e.g., Sheet2!$E$2:$F$5).

How do I hide the #N/A error when the drop-down cell is blank?

You can wrap your VLOOKUP formula in an IFERROR function to return a blank space instead of an error. For example: =IFERROR(VLOOKUP(B2,$E$2:$F$5,2,FALSE), ""). If using XLOOKUP, simply add "" as the fourth argument.

How do I create the drop-down list in the first place?

Select the cell where you want the drop-down list to appear. Go to the Data tab, click Data Validation, choose 'List' under the Allow dropdown, and select the range containing your list items as the source.

Excel & Data Problems