Function Problems

How to Update Another Cell Using Excel Data Validation

Elise Williams Oct 12, 2026 868 views

Question details

The user wants to create a drop-down list in one cell and have another cell automatically update to display a related value based on the selected item.

How to Update Another Cell Using Excel Data Validation

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic forms, invoices, or dashboards where selecting an item from a drop-down list instantly retrieves its corresponding data.
Observed behavior: A drop-down list is successfully created in one cell, and a lookup function like VLOOKUP or XLOOKUP is applied to an adjacent cell to fetch and display the matched value.

Before you start

Ensure your reference data is properly organized in a continuous range or table, with the items for the drop-down list in the first column and their related values in adjacent columns.

Solution 1Recommended

Use Data Validation with VLOOKUP

Combine a Data Validation drop-down list with the VLOOKUP function to automatically pull related data based on the user's selection.

VLOOKUP is a classic Excel function used to search for a value in the first column of a table array and return a value in the same row from another column.

Create the drop-down list

Select the cell where you want the drop-down (e.g., B1). Go to the Data tab on the ribbon, click Data Validation, choose 'List' from the Allow menu, and set the Source to your reference text range (e.g., =$D$1:$D$10).

Enter the VLOOKUP formula

Select the cell where you want the related value to appear (e.g., A1). Type the formula =VLOOKUP(B1,$D$1:$E$10,2,FALSE).

Test the automatic update

Click the drop-down arrow in cell B1, select a different item, and watch cell A1 automatically update to show the matching value from column E.

Exact Match: Using FALSE as the fourth argument in VLOOKUP ensures that the formula only returns a result if it finds an exact match.

Solution 2

Use Data Validation with XLOOKUP

Utilize the modern XLOOKUP function to retrieve related data, which offers more flexibility than VLOOKUP.

Advanced Spreadsheet Features

Create Dynamic Worksheets Effortlessly with WPS Office

WPS Spreadsheet fully supports Data Validation, VLOOKUP, and the modern XLOOKUP function. You can easily build dynamic data entry forms and interactive dashboards exactly like you do in Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
2. Create the Drop-down List: Select your input cell, navigate to the Data tab, click 'Validation', and choose 'List' to set your source data range.
3. Apply the Lookup Formula: Select the adjacent target cell and enter your =VLOOKUP or =XLOOKUP formula to automatically fetch matching data.

Seamless compatibility with Microsoft Excel formulas and .xlsx formats.Intuitive Data Validation menus for creating instant drop-down lists.Supports both classic VLOOKUP and advanced XLOOKUP functions.Free and lightweight software for professional spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return an #N/A error after selecting from the drop-down?

This error occurs when the formula cannot find an exact match. It often happens if there are hidden leading or trailing spaces in your reference data. Using the TRIM function on your data or ensuring exact text matches can resolve this.

Can I pull multiple related values from a single drop-down selection?

Yes, you can use multiple VLOOKUP or XLOOKUP formulas in different cells. For VLOOKUP, simply change the column index number in each formula to point to the different columns you want to retrieve.

How do I update another cell if the drop-down list is empty?

You can wrap your lookup formula in an IFERROR or IF function to display a blank or default message. For example: =IF(B1="","",VLOOKUP(B1,$D$1:$E$10,2,FALSE)).

Excel & Data Problems