How to Select Multiple Items in an Excel Drop-Down and Return Related Text
Question details
The user needs a method to select multiple items from a single drop-down list and simultaneously retrieve or combine the related text descriptions for all selected items.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to create a form or report where a user can choose multiple licenses or items from a single cell, and then display the corresponding descriptions for all choices.
- Observed behavior
- Standard drop-down lists only allow one selection at a time, and a standard VLOOKUP formula only returns the related text for a single matching item rather than multiple selections.
Since native Excel drop-downs only allow single selections, you will need to enable the Developer tab to use VBA or familiarize yourself with Power Query before proceeding.
Use a VBA Macro to Allow Multiple Selections in a Drop-Down
VBA can capture when a user selects a new item in an existing drop-down and append it to the current cell value, allowing you to combine related text.
By default, Excel's Data Validation feature replaces the existing cell value when a new item is selected. Using a Worksheet_Change event in VBA allows you to intercept the selection, keep the old values, and append the new one separated by a comma.
Once the cell contains a comma-separated list of selected items, a custom VBA function is often required to look up and concatenate the related descriptions from your lookup table.
Select the target cell, go to the Data tab, click Data Validation, choose 'List', and define your source range.
Right-click the sheet tab containing your drop-down list and select 'View Code' to open the Microsoft Visual Basic for Applications window.
Paste a Worksheet_Change event script designed to concatenate values if the target cell address matches your drop-down cell.
Use a User Defined Function (UDF) in VBA to split the selected values, look up the description for each item in your source table, and output the combined text string.
Use Power Query and PivotTables as a Non-VBA Alternative
If you prefer not to use VBA, you can load your dataset into Power Query and use a PivotTable with Slicers to select and display related text for multiple items.
Create Advanced Drop-Downs and Macros in WPS Spreadsheet
WPS Spreadsheet fully supports advanced data validation, complex array formulas, and VBA macros, making it easy to build multi-select drop-downs and retrieve related text in a single form.
- 1. Create Data Validation: Open your spreadsheet in WPS Office, navigate to the Data tab, and click 'Data Validation' to establish your initial drop-down list.
- 2. Access the Developer Tools: Go to the Developer tab to open the VBA Editor and input your multi-selection concatenation code.
- 3. Apply Lookup Formulas: Use powerful built-in lookup functions or array formulas in WPS Spreadsheet to fetch the associated text for your selected items.
- 4. Save Your Work: Save the document as an .xlsm file to preserve your macro functionality and ensure seamless sharing.
Frequently Asked Questions
Can I use VLOOKUP to return multiple values for different selected items?
Standard VLOOKUP is designed to return the first matching value for a single lookup criterion. To return related text for multiple items simultaneously, you must use VBA to combine the results, or utilize newer array functions like FILTER and TEXTJOIN if supported by your spreadsheet version.
Why does my Excel drop-down only allow one selection?
Data Validation drop-down lists in spreadsheet applications natively support only single selections to maintain data integrity. Changing this behavior to allow multi-selection requires custom VBA scripting triggered by a Worksheet_Change event.
How do I save a workbook that contains VBA macros?
You must save the file in a macro-enabled format, such as .xlsm. If you save it as a standard .xlsx file, all VBA code that enables the multiple drop-down selections will be permanently removed.
What is an alternative to VBA for handling multiple selections?
Instead of modifying cell drop-downs, you can insert a 'List Box' form control with the 'MultiSelect' property enabled from the Developer tab, or use PivotTables combined with Slicers to visually filter and display multiple related records without any coding.
