Display a Department Next to an Excel Drop-Down List
Question details
Create a name drop-down list and automatically display the selected person's department in an adjacent cell.

- 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.
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.
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.
Enter the names in E2:E11 and the matching departments in F2:F11, keeping each name and department on the same row.
Select A2, open Data > Data Validation, choose List under Allow, and enter =$E$2:$E$11 in the Source box. Select OK.
Select B2 and enter =IF(A2="","",IFERROR(VLOOKUP(A2,$E$2:$F$11,2,FALSE),"")), then press Enter.
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.

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. Open the spreadsheet: Open WPS Spreadsheet and create or open a workbook. Enter names in E2:E11 and their departments in F2:F11.
- 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. Enter the lookup formula: Select B2 and enter =IF(A2="","",IFERROR(VLOOKUP(A2,$E$2:$F$11,2,FALSE),"")), then press Enter.
- 4. Choose a name: Use the drop-down in A2 to select a name and check that its department appears in B2.

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.




