Function Problems

How to Automatically Fill an Employee ID from an Excel Drop-Down

Algirdas Jasaitis Oct 10, 2026 871 views

Question details

Retrieve an employee ID from a reference table automatically when selecting an employee name from a drop-down list.

Automatically Fill an Employee ID from an Excel Drop-Down List

Product: Spreadsheet
Device & OS: not provided
Scenario: Managing employee data or creating a form where picking a name from a data validation drop-down automatically fetches the corresponding ID.
Observed behavior: The target cell displays the correct employee ID instantly upon drop-down selection without requiring manual data entry.

Before you start

Ensure you have a reference table prepared (e.g., on Sheet2) with employee names in the first column and their corresponding IDs in the second column.

Solution 1Recommended

Use the VLOOKUP Function

The standard and most widely compatible method to look up and retrieve data based on a drop-down selection.

VLOOKUP searches for a specific value in the first column of a table array and returns a value in the same row from another column. It is perfect for matching names to IDs.

Select the target cell

Click the cell where you want the Employee ID to automatically appear (for example, cell D3).

Enter the formula

Type the formula =VLOOKUP(C3, Sheet2!A:B, 2, FALSE) into the formula bar. In this example, C3 is your drop-down list, Sheet2!A:B is your reference table, and 2 tells Excel to pull data from the second column.

Apply and test

Press Enter to apply the formula. Change the employee name in your drop-down list (C3) to verify that the ID updates automatically.

Exact Match Required: Using 'FALSE' at the end of the VLOOKUP formula ensures Excel only returns a result if it finds an exact match for the employee name.

Solution 2

Use the Modern XLOOKUP Function

A newer, more flexible alternative to VLOOKUP that does not require the search value to be in the first column.

WPS Spreadsheet Solution

Automate Data Entry with Drop-Downs in WPS Office

WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP and XLOOKUP, allowing you to build automated forms and retrieve employee IDs just as easily as in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open your employee data document.
2. Create a drop-down list: Select the name cell, go to Data > Data Validation, choose 'List', and select your source names.
3. Insert the lookup formula: Click the ID cell and type your =VLOOKUP or =XLOOKUP formula to link the reference table.
4. Save and share: Save your automated form in .xlsx format for seamless sharing with Microsoft Office users.

100% compatible with Microsoft Excel (.xlsx) formats and formulas.Intuitive Data Validation tool for creating drop-down lists in seconds.Free, lightweight software with seamless cross-platform syncing.Built-in function library to easily insert and troubleshoot formulas.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error?

This usually happens if the selected name in the drop-down doesn't exactly match the name in your reference table. Commonly, this is caused by accidental trailing spaces. Check both the drop-down source data and the reference table for extra spaces.

How do I hide the #N/A error when the drop-down is blank?

You can wrap your lookup formula in the IFERROR function to handle blank states gracefully. Use this formula: =IFERROR(VLOOKUP(C3, Sheet2!A:B, 2, FALSE), ""). This tells the spreadsheet to display a blank cell instead of an error until a name is selected.

Can I look up data if the employee names are not in the first column?

Yes, but VLOOKUP only searches the first column of the specified range. If your names are in column B and IDs are in column A, you should use the XLOOKUP function (=XLOOKUP(C3, B:B, A:A)) or a combination of the INDEX and MATCH functions.

How do I create the drop-down list for the employee names?

Select the cell where you want the drop-down to appear. Go to the Data tab on the ribbon and click Data Validation. In the Allow drop-down, select 'List', then click in the Source box and highlight the range containing your employee names. Click OK.

Excel & Data Problems