How to Show Two Columns in an Excel Dropdown but Return One Value
Question details
Display multiple pieces of related information (like a cost code and location) in an Excel dropdown list while ensuring only the primary identifier is inputted into the target cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a searchable dropdown list in a worksheet where users need to see additional context to make the right choice, but the system or layout requires only the core code to be recorded.
- Observed behavior
- Native Excel data validation dropdowns only support selecting and displaying a single column's value, and they cannot natively show a multi-column view that yields a single specific value upon selection.
Ensure your reference data is organized in clear, adjacent columns on a separate sheet (e.g., Cost Codes in Column A, Locations in Column B), and consider making a backup copy of your workbook if you plan to implement VBA solutions.
Use a Helper Column and Text Extraction Formula
Combine both data points into a single text string for the dropdown list, and use a formula in an adjacent cell to extract only the needed value.
Since native Excel Data Validation cannot display multiple columns, the most reliable workaround is to concatenate (join) the columns into one helper column. Once the user selects the combined string, you can use formulas to parse out the primary value.
On the sheet containing your source data (Sheet 2), insert a new column. Use a concatenation formula like `=A2 & " - " & B2` to combine the Cost Code and Location. Drag this down for all rows.
Go to your main worksheet (Sheet 1) and select the cell where you want the dropdown. Navigate to Data > Data Validation, choose 'List' from the Allow dropdown, and select your new helper column range as the Source.
In the cell where you actually need the isolated Cost Code to appear, enter a formula to extract the characters before the delimiter: `=LEFT(C2, FIND(" - ", C2)-1)` (assuming C2 is your dropdown cell). This pulls just the cost code out of the combined selection.
Use a VBA Worksheet Change Event
Implement a VBA macro that automatically strips out the secondary information the moment a user makes a selection from the combined dropdown list.
Create Advanced Dropdown Lists Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports Data Validation, concatenation, and text extraction formulas just like Excel. You can quickly set up helper columns to display multi-column contexts in your dropdowns without needing a premium subscription.
- 1. Open your data file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the cost codes and locations.
- 2. Create a combined reference column: Use the `&` operator in an empty column to merge your data, for example: `=A2 & " | " & B2`.
- 3. Apply Data Validation: Select your target cell, navigate to the Data tab, click Data Validation, and set your Source to the newly created merged column.
- 4. Extract your final value: Use the `LEFT` and `FIND` functions in your final destination cell to isolate the required code from the user's dropdown selection.
Frequently Asked Questions
Can I natively show two separate columns in a standard Excel dropdown without combining them?
No, standard Data Validation lists in Excel and WPS Spreadsheet do not natively support multi-column display. You must either concatenate the data into a helper column or build a custom VBA UserForm with a Combo Box control.
Why does my LEFT and FIND extraction formula return a #VALUE! error?
This usually occurs if the text selected in the dropdown does not contain the exact separator you specified in your FIND function. Check that your helper column combination formula and your extraction formula use the exact same delimiter (e.g., spaces and hyphens).
Will hiding the helper column break the Data Validation dropdown?
No. Once you have set the source range for your Data Validation list, you can safely hide the column containing the combined text. The dropdown will continue to read the data correctly.
