Function Problems

Return a Data Table from Multiple Excel Drop-Down Selections

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

Display the appropriate data table based on selections from three drop-down lists.

Return a Data Table from Multiple Excel Drop-Down Selections
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has separate drop-down selections for month, year, and activity type.
Observed behavior
The displayed table should update to match all three selections.
Before you start

Check that your Excel version supports the FILTER function and spilled arrays. Make sure month and year values use the same formats in the drop-down lists and source data, and leave the output area clear.

Solution 1Recommended

Filter a combined data table by all three selections

Use this approach when the source tables can be combined into one consistently structured list.

1
Combine the source data

On a sheet named Data, place month, year, and activity type in columns A–C and the fields to display in columns D–F. Include those three selection values on every data row.

2
Create the drop-down lists

On the output sheet, select B2, B3, and B4 in turn and use Data > Data Validation > List to create the month, year, and activity drop-downs from your respective source lists.

3
Add output headers

Enter the headers for the fields in Data columns D–F in cells E6:G6. Keep the cells below them empty for the results.

4
Enter the filter formula

In E7, enter =FILTER(Data!D2:F1000,(Data!A2:A1000=B2)*(Data!B2:B1000=B3)*(Data!C2:C1000=B4),"No matching records"). Adjust the row limits and result columns to fit your data.

5
Test the selections

Choose a month, year, and activity in B2:B4. The matching rows should spill from E7; clear any cells blocking the output if Excel shows a spill error.

Filter a combined data table by all three selections
Work with selection-based tables in WPS

Filter Selected Data Tables in WPS Spreadsheet

Combine the source data in WPS Spreadsheet, create three validation lists, and use FILTER to display matching rows when your WPS version supports the function.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Organize the data: On a Data sheet, combine the source rows with month, year, and activity in columns A–C and result fields in columns D–F.
  3. 3. Create the lists: Select B2, B3, and B4 on the output sheet and use Data > Data Validation to set up a list for each selection.
  4. 4. Filter the results: In an empty output cell, enter =FILTER(Data!D2:F1000,(Data!A2:A1000=B2)*(Data!B2:B1000=B3)*(Data!C2:C1000=B4),"No matching records"). Select values in the three lists and check that the results appear.
Display rows matching month, year, and activity selectionsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return no matching records?

Check that all three selections match the source values exactly. In particular, ensure years are consistently stored as numbers or text, and check month and activity labels for extra spaces.

Why does Excel show a spill error?

FILTER needs empty cells for its results. Clear the cells below and beside the formula, and place the formula outside an Excel table if necessary.

Can I return separate tables without combining their rows?

Yes. Create a mapping sheet with the three selection values and a named range for each table, then use FILTER to find the name and INDIRECT to return its range. Each combination must have one mapping.

What if my spreadsheet does not recognize FILTER?

Check whether your Excel or WPS Spreadsheet version supports FILTER and dynamic arrays. If it does not, use Data > Filter on a combined source list to filter the three columns manually.