How to Fix Excel Cascading Drop-Down Lists Omitting Values
Question details
Resolve an issue where cascading drop-down lists fail to display expected options due to formula lookup errors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using OFFSET and MATCH formulas in data validation to create dependent cascading drop-down lists.
- Observed behavior
- The dependent drop-down list omits options because the MATCH formula fails to locate reference values due to extra spaces or missing data.
Verify that your lookup tables and reference data ranges are fully visible and that no columns or rows containing your drop-down list items have been accidentally hidden.
Remove Extra Spaces from Lookup Values
Eliminate hidden spaces that cause exact MATCH formulas to fail in data validation.
When using the MATCH function for exact matches (match type 0), even a single trailing or leading space will cause the formula to return an error. Cleaning your data ensures the cascading drop-down list can accurately map to the correct lookup row.
Select the cells containing your primary drop-down list values and the corresponding lookup row headers (e.g., Division names like 'Aerospace').
In an adjacent blank column, use the TRIM function to remove any leading or trailing spaces by typing =TRIM(A2) and pressing Enter.
Copy the newly trimmed values, right-click the original data cells, and choose 'Paste Special' > 'Values' to overwrite the incorrect data with the clean text.
Click on the cell with your primary drop-down list, select a value, and verify that the dependent list now successfully populates the options.
Ensure All Values Exist in Lookup Rows
Check that every category selected in the primary drop-down list has a corresponding header in your reference data.
Create Reliable Cascading Drop-Down Lists in WPS Spreadsheet
WPS Spreadsheet makes it simple to set up data validation and complex formulas like OFFSET and MATCH. Easily clean your data and create error-free cascading drop-down lists.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing your data validation rules.
- 2. Access Data Validation: Select the cell for your dependent drop-down list, navigate to the 'Data' tab, and click on 'Validation'.
- 3. Configure the list settings: Choose 'List' from the Allow criteria and input your OFFSET and MATCH formula in the Source box.
- 4. Apply and test: Click 'OK' to apply the settings, then test your cascading drop-down list to ensure all values display correctly.
Frequently Asked Questions
Why is my dependent drop-down list showing blank?
A blank dependent drop-down list usually occurs when the reference range in your OFFSET or INDIRECT formula is incorrect, or if the primary drop-down selection doesn't exactly match the named range or lookup header due to typos or spaces.
Does the MATCH function ignore spaces in Excel?
No. When configured for an exact match (using 0 as the third argument), the MATCH function evaluates spaces as standard characters. Leading, trailing, or double spaces will cause the match to fail.
How do I make my drop-down list ignore blanks?
In the Data Validation dialog box, ensure the 'Ignore blank' checkbox is selected. This prevents users from selecting an empty cell if the source list happens to contain blank values.
