Function Problems

How to Auto-Populate Excel Cells from a Dropdown Selection

Maira Mehtab Sep 20, 2026 868 views

Question details

The user wants to select a unique identifier, such as an employee number, from a dropdown list and have other related details automatically fill into adjacent cells using data from another worksheet.

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic data entry sheets or forms where selecting a unique ID retrieves and displays corresponding information from a master database.
Observed behavior: When an employee number is selected in column A, the corresponding employee information needs to automatically appear in columns B and C without manual data entry.

Before you start

Before applying any formulas, verify that the employee numbers in your source table exactly match the format (text or number) of the items in your dropdown list to prevent lookup errors.

Solution 1Recommended

Use VLOOKUP to Auto-Populate Adjacent Cells

VLOOKUP is the most straightforward method when the lookup value (like an employee number) is located in the first column of your source data table.

The VLOOKUP function searches for a specific value in the first column of a table array and returns a value in the same row from a specified column. It is perfect for standard databases where identifiers are listed on the far left.

Select the destination cell

Click on cell B2 (or the cell where you want the first piece of auto-populated data to appear).

Enter the VLOOKUP formula

Type the formula =VLOOKUP(A2,Sheet2!A:C,2,FALSE). In this formula, A2 is your dropdown selection, Sheet2!A:C is the range containing your employee data, 2 is the column index for the data you want to retrieve, and FALSE ensures an exact match.

Adjust for additional columns

To populate column C, select cell C2 and enter =VLOOKUP(A2,Sheet2!A:C,3,FALSE), changing the column index number from 2 to 3.

Absolute References: If you plan to drag the formula down to apply it to multiple rows, make sure to lock your table array using absolute references (e.g., Sheet2!$A$1:$C$100).

Solution 2

Use INDEX and MATCH for Flexible Lookups

INDEX and MATCH is a more robust alternative to VLOOKUP, ideal for situations where the lookup column is not the first column in your data table.

WPS Spreadsheet Data Management

Auto-Populate Cells Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data validation and lookup functions like VLOOKUP, XLOOKUP, and INDEX/MATCH, allowing you to seamlessly link worksheets and automate data entry.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your main sheet and data source sheet.
2. Create a dropdown list: Select the cells in column A, go to the 'Data' tab, click 'Validation', choose 'List', and select your employee numbers.
3. Enter the lookup formula: Click on the adjacent cell (B2) and type your preferred lookup formula, such as =VLOOKUP(A2,Sheet2!A:C,2,0).
4. Apply to multiple rows: Press Enter to fetch the data, then drag the fill handle down to apply the formula to the rest of the column.

100% compatible with Microsoft Excel formulas (.xlsx and .xls)Intuitive Data Validation tools for quick dropdown list creationLightweight and fast performance for processing large datasetsBuilt-in formula suggestions to prevent syntax errors

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error after selecting from the dropdown?

This usually happens if the selected dropdown value doesn't exactly match the source table value. Check for leading or trailing spaces, and ensure the data types match (e.g., both are formatted as text, or both are formatted as numbers).

How do I create the initial dropdown list for the employee numbers?

Select the cell where you want the dropdown, go to the Data tab on the ribbon, and click Data Validation. Under the Allow criteria, choose 'List', and then select the range in your worksheet that contains your employee numbers.

Can I auto-populate data across entirely different workbooks instead of worksheets?

Yes. You can reference another workbook in your formula (for example, =VLOOKUP(A2,[Database.xlsx]Sheet1!A:C,2,FALSE)). However, the source workbook must remain in its specified file path, or the link may break.

Does WPS Spreadsheet support the newer XLOOKUP function for this task?

Yes, WPS Spreadsheet fully supports XLOOKUP. It provides an easier and more flexible way to auto-populate cells because it allows you to search in any direction and defaults to exact matches without needing the 'FALSE' argument.

Excel & Data Problems