Automatically Populate an Excel Cell from a Data Validation List
Question details
Automatically return a related value in column B when an item is selected from a data validation list in column A.

- 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.
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.
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.
Enter animals in E2:E4 and their classifications in F2:F4, such as Cat and Mammal, Eagle and Bird, and Frog and Amphibian.
Select E2:F4, click the Name Box to the left of the formula bar, type Animals, and press Enter.
Select A2:A100, then choose Data > Data Validation. Under Allow, select List, enter =$E$2:$E$4 in Source, and click OK.
Enter =IFERROR(VLOOKUP(A2,Animals,2,FALSE),"") in B2. Drag the fill handle down column B to apply the formula to the remaining rows.
Open the drop-down in A2 and select an animal. Its classification should appear in B2.

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. Open a spreadsheet: Open WPS Office, launch Spreadsheets, and create or open a workbook.
- 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. 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. 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.

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.




