Function Problems

Automatically Populate an Excel Cell from a Data Validation List

Huma Ashraf ChHuma Ashraf Ch Nov 20, 2026 868 views

Question details

Automatically return a related value in column B when an item is selected from a data validation list in column A.

Automatically Populate an Excel Cell from a Data Validation List
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a drop-down list of animals, and the user wants the corresponding classification to appear beside each selection.
Observed behavior
Selecting an animal in column A should display its classification in column B.
Before you start

Prepare a two-column list with each drop-down item in the first column and its corresponding value in the second. Keep each item unique so the lookup returns the intended match.

Solution 1Recommended

Use VLOOKUP to Return a Value from a Data Validation Selection

Use a named lookup range and an exact-match formula to populate the cell next to each selection.

1
Create the lookup list

Enter animals in E2:E4 and their classifications in F2:F4, such as Cat and Mammal, Eagle and Bird, and Frog and Amphibian.

2
Name the lookup range

Select E2:F4, click the Name Box to the left of the formula bar, type Animals, and press Enter.

3
Create the drop-down list

Select A2:A100, then choose Data > Data Validation. Under Allow, select List, enter =$E$2:$E$4 in Source, and click OK.

4
Enter and copy the formula

Enter =IFERROR(VLOOKUP(A2,Animals,2,FALSE),"") in B2. Drag the fill handle down column B to apply the formula to the remaining rows.

5
Test a selection

Open the drop-down in A2 and select an animal. Its classification should appear in B2.

Use VLOOKUP to Return a Value from a Data Validation Selection
How the formula works: VLOOKUP searches the first column of Animals for an exact match and returns the value from its second column. IFERROR displays a blank if no match is found.
Try it in WPS Office

Populate Related Values with WPS Spreadsheets

Create a drop-down list and use VLOOKUP in WPS Spreadsheets to return a related value for each selection.

  1. 1. Open a spreadsheet: Open WPS Office, launch Spreadsheets, and create or open a workbook.
  2. 2. Build the lookup table: Enter animals in E2:E4 and their classifications in F2:F4. Select E2:F4, type Animals in the Name Box, and press Enter.
  3. 3. Add the drop-down: Select A2:A100 and choose Data > Data Validation. Select List, enter =$E$2:$E$4 as the source, and confirm.
  4. 4. Add the lookup formula: Enter =IFERROR(VLOOKUP(A2,Animals,2,FALSE),"") in B2, then drag the fill handle down. Choose an animal in column A to test the result.
Automatically return a classification beside a drop-down selectionOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and data validation toolsGet started with a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the result cell stay blank after I select an item?

Check that the selected text appears in the first column of the Animals range and that the range includes both lookup columns. IFERROR hides lookup errors by displaying a blank.

Can I add more animals to the drop-down list?

Add the animals and classifications below the existing lookup table, then update the list source in Data > Data Validation and expand the Animals named range using Formulas > Name Manager.

What does FALSE mean in the VLOOKUP formula?

FALSE tells VLOOKUP to find an exact match for the selected value in the first column of the Animals range.

Can I return a different related value?

Yes. Put the desired values in the second column of the Animals range, or expand the range and change the VLOOKUP column index from 2 to the number of the column you want to return.