Function Problems

WPS Spreadsheets: Create a Dependent Drop-Down List with Multiple Results

Algirdas Jasaitis Nov 12, 2026 868 views

Question details

The user needs a data-validation drop-down that displays all airports associated with the state selected in another cell.

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: A worksheet contains state values in one column and airport values in another. The user wants the airport list to update automatically when a state is selected, without hard-coding a long IF formula or using VBA.
Observed behavior: The current INDEX and MATCH formula returns only the first matching airport instead of providing multiple matching results for the selected state.

Before you start

Confirm which cell contains the state selection and identify the source columns for states and airports. Keep the source data ranges consistent, and use a helper area on the same sheet or a separate worksheet for the filtered results.

Solution 1Recommended

Use FILTER in a helper range and link the spilled results to data validation

Use this method when your WPS Spreadsheets version supports dynamic-array formulas and you want the dependent list to update automatically.

INDEX and MATCH return one matching value, so they cannot create a complete list by themselves. FILTER returns every airport whose state matches the selection in B7, allowing the data-validation list to use the resulting range.

Create the filtered airport list

Select an empty helper cell, such as J2, and enter =FILTER('CONUS Travel Source'!$H$3:$H$385,'CONUS Travel Source'!$B$3:$B$385=$B$7). Press Enter so WPS Spreadsheets spills all matching airports below the helper cell.

Select the destination cell

Click the cell where users should choose an airport, then open the Data tab and select Data Validation.

Set the validation source

In the Data Validation dialog, choose List as the validation type and enter =J2# as the source, replacing J2 with the actual helper cell. Confirm the setting with OK.

Test another state

Change the state in B7 and open the airport drop-down. The list should refresh to show only airports matching the newly selected state.

If the spill operator is unavailable: If your version does not accept the # spill reference in Data Validation, select a sufficiently large helper range such as $J$2:$J$50 or create a named range that refers to the filtered results, then use that range as the list source.

Solution 2

Build a dynamic range with OFFSET for sorted source data

Use this alternative when the source rows for each state are grouped together and your spreadsheet version does not support FILTER.

Create Dynamic Dependent Lists in WPS Spreadsheets

WPS Spreadsheets can create a dependent airport drop-down with FILTER-based helper results or a dynamic OFFSET range. Its familiar Data Validation tools and Excel-compatible formulas make it easy to migrate this workflow without VBA.

1. Prepare the source data: Open WPS Spreadsheets and verify the state and airport columns on the CONUS Travel Source sheet.
2. Generate matching results: Enter a FILTER formula in an empty helper cell to return all airports matching the state selector.
3. Configure the drop-down: Select the destination cell, open Data > Data Validation, choose List, and reference the helper spill range.
4. Check the dependent behavior: Change the selected state and confirm that the airport options refresh automatically.

Automatically show multiple airport choices for the selected stateAvoid long nested IF formulas and VBA macrosUse familiar spreadsheet formulas and Data Validation controlsOpen and save Microsoft Excel .xlsx files with compatible formatting and formulas

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDEX and MATCH return only one airport?

INDEX and MATCH normally return a single value from the first matching row. To return multiple airports, use FILTER in a helper cell or create a dynamic range with OFFSET and COUNTIF.

Where should the FILTER formula be placed?

Place it in an unused helper cell, such as J2, on the current sheet or a separate worksheet. Use =FILTER('CONUS Travel Source'!$H$3:$H$385,'CONUS Travel Source'!$B$3:$B$385=$B$7), then reference the resulting range in Data Validation.

Can the airport list update when the state changes?

Yes. The FILTER condition refers to the state selector cell, such as B7. When B7 changes, the helper results recalculate and the dependent list updates.

What should I do if WPS does not accept the # spill reference?

Use a fixed helper range that is large enough for the maximum number of airports, or define a named range for the filtered results and select that name as the Data Validation source.

Does the OFFSET method require sorted data?

Yes, the OFFSET approach works most reliably when records for each state are grouped together. If the source rows are not grouped, use FILTER instead.

Excel & Data Problems