Function Problems

Display a Department Next to an Excel Drop-Down List

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Create a name drop-down list and automatically display the selected person's department in an adjacent cell.

Display a Department Next to an Excel Drop-Down List
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs a name selection cell and a neighboring cell that looks up the corresponding department.
Observed behavior
Selecting a name from the list should display its matching department next to it.
Before you start

Prepare a two-column lookup table with names in the first column and their departments in the second. The example below uses names in E2:E11, departments in F2:F11, and the drop-down cell A2.

Solution 1Recommended

Use Data Validation and VLOOKUP to Return the Department

Create a list of names in A2 and use a formula in B2 to show the corresponding department.

1
Enter the lookup table

Enter the names in E2:E11 and the matching departments in F2:F11, keeping each name and department on the same row.

2
Create the name list

Select A2, open Data > Data Validation, choose List under Allow, and enter =$E$2:$E$11 in the Source box. Select OK.

3
Add the lookup formula

Select B2 and enter =IF(A2="","",IFERROR(VLOOKUP(A2,$E$2:$F$11,2,FALSE),"")), then press Enter.

4
Test the selection

Open the drop-down arrow in A2 and select a name. Confirm that B2 displays the department from the same row in the lookup table.

Use Data Validation and VLOOKUP to Return the Department
Adjust the references: Change the cell references to match your worksheet. If a name appears more than once, VLOOKUP returns the department for the first match.
WPS Spreadsheet

Create a Name Drop-Down and Department Lookup in WPS Spreadsheet

Use data validation and a VLOOKUP formula in WPS Spreadsheet to display a department when you select a name.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and create or open a workbook. Enter names in E2:E11 and their departments in F2:F11.
  2. 2. Set up the drop-down: Select A2, go to Data > Data Validation, choose List, and set the source to =$E$2:$E$11.
  3. 3. Enter the lookup formula: Select B2 and enter =IF(A2="","",IFERROR(VLOOKUP(A2,$E$2:$F$11,2,FALSE),"")), then press Enter.
  4. 4. Choose a name: Use the drop-down in A2 to select a name and check that its department appears in B2.
Automatically return a department for a selected nameOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and data validation controlsGet started with WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Can the name and department table be on another worksheet?

Yes. Create a named range for the name list and use that name as the Data Validation source. In the VLOOKUP formula, reference the other sheet's table, for example =VLOOKUP(A2,'Lookup Sheet'!$E$2:$F$11,2,FALSE).

Why is the department cell blank after I select a name?

The example formula hides lookup errors with IFERROR. Check that the selected name exists in the first column of the lookup range and that the range includes the department column. You can temporarily remove IFERROR to see the underlying error.

What happens if two people have the same name?

VLOOKUP returns the first matching name in the lookup table. Use a unique identifier, such as an employee ID, in the drop-down and the first column of the lookup table to distinguish people with the same name.

How can I use the same lookup for more rows?

Copy the data validation cell A2 to the other selection cells, then fill the formula in B2 down alongside them. The A2 reference changes by row, while the dollar signs keep the lookup range fixed.