Return a Data Table from Multiple Excel Drop-Down Selections
Question details
Display the appropriate data table based on selections from three drop-down lists.

- 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.
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.
Filter a combined data table by all three selections
Use this approach when the source tables can be combined into one consistently structured list.
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.
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.
Enter the headers for the fields in Data columns D–F in cells E6:G6. Keep the cells below them empty for the results.
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.
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.

Look up a named table range with FILTER and INDIRECT
Use a mapping sheet when the source tables must remain in separate locations.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. 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. 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.

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.




