How to Return a Value from Another Excel Worksheet Using a Drop-Down List
Question details
The user needs to automatically populate a cell with a first name from another worksheet based on a last name selected in a drop-down list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Retrieving corresponding data from a separate worksheet dynamically based on a user's drop-down list selection.
- Observed behavior
- A drop-down list of last names is located in cell C8. The goal is to display the matching first name in cell C9 by pulling the data from the 'Winter Vacation' worksheet.
Ensure that the source data range in your referenced worksheet is properly structured, with the lookup value (last name) located in the very first column of that range.
Use the VLOOKUP Function to Retrieve Data Across Worksheets
The VLOOKUP function allows you to search for a specific value in a column and return a corresponding value from another column in the same row, even across different worksheets.
VLOOKUP stands for Vertical Lookup. It requires four parameters: a lookup value, the table array (range containing the data), the column index number to return, and an exact or approximate match indicator.
Click on cell C9 (or your desired target cell) where you want the retrieved first name to appear.
Type =VLOOKUP(C8, 'Winter Vacation'!A4:C32, 2, FALSE) into the formula bar.
In this formula, C8 is your selected drop-down value. 'Winter Vacation'!A4:C32 is the data range on the other worksheet. 2 specifies the second column (which contains the first names). FALSE ensures an exact match.
Press the Enter key. Cell C9 will now automatically update with the corresponding first name whenever a new last name is selected in the drop-down list.
Easily Manage Drop-Down Lists and Formulas with WPS Spreadsheet
WPS Spreadsheet provides a robust, user-friendly environment for managing complex formulas like VLOOKUP and creating dynamic drop-down lists. It is highly compatible with Microsoft Excel files, making data retrieval seamless.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing Excel (.xlsx) file containing the drop-down list and the source data.
- 2. Select the destination cell: Click the cell (e.g., C9) where you want the retrieved data to be dynamically displayed.
- 3. Insert the VLOOKUP formula: Type =VLOOKUP( and use your mouse to select the drop-down cell, switch to the other worksheet to highlight the data range, input the column index number, and type FALSE).
- 4. Press Enter and test: Hit Enter to apply the formula. Change the selection in your drop-down list to verify that the corresponding data updates dynamically.
Frequently Asked Questions
Why does my VLOOKUP formula return an #N/A error?
The #N/A error typically occurs if the lookup value is not found in the first column of your specified range, if there are trailing spaces in your text, or if the data types do not match (e.g., text vs. number).
How do I create the drop-down list in the first place?
Select the cell where you want the list, navigate to the Data tab, click Data Validation, choose 'List' from the Allow drop-down, and select the range containing your source items.
What does the 'FALSE' in the VLOOKUP formula mean?
FALSE tells the VLOOKUP function to look for an exact match. If you omit it or use TRUE, the function might return an approximate match, which is usually not desired for text values like names.
Can I use XLOOKUP instead of VLOOKUP?
Yes, if your spreadsheet version supports it, XLOOKUP is a more flexible alternative. It does not require the lookup value to be in the first column and defaults to an exact match automatically.
