Use Multiple Drop-Down Lists to Return Excel Response Outputs
Question details
Create a worksheet that uses four selected drop-down list criteria to retrieve 16 related response outputs.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs a maintainable decision or response tool based on combinations of four criteria categories.
- Observed behavior
- The worksheet should find the row matching all four selected criteria and display its corresponding response columns.
Arrange the four selected criteria in dedicated input cells, and ensure every valid criteria combination is listed once in a structured lookup table. Keep the criteria values in the lookup table consistent with the values used by the drop-down lists.
Create a multi-criteria lookup table with INDEX and MATCH
Use this approach when you need a flexible, maintainable formula that returns a response from the row matching all four drop-down selections.
Create one lookup table with four criteria columns followed by 16 response columns. Each row represents a valid combination of the four criteria and contains the outputs that should be returned for that combination.
The formula below matches all four criteria at once. Enter it in the first response cell, then adjust the response range for each additional output column.
On a separate worksheet, create columns for Criteria 1, Criteria 2, Criteria 3, and Criteria 4, followed by Response 1 through Response 16. Add one row for every valid criteria combination.
Select each criteria input cell, then choose Data > Data Validation. Set Allow to List and select the source range containing the allowed values for that criterion.
In the output cell, enter a formula such as =IFERROR(INDEX($E$2:$E$100,MATCH(1,($A$2:$A$100=$H$2)*($B$2:$B$100=$H$3)*($C$2:$C$100=$H$4)*($D$2:$D$100=$H$5),0)),""). Replace A:D with the lookup criteria columns, H2:H5 with the four selected drop-down cells, and E:E with the response column to return.
Copy the formula to the other response cells and change the INDEX response range from the first response column to each of the remaining response columns. If your response columns are adjacent, copy across and let the response range shift by column.
Use a helper key with XLOOKUP for a simpler formula
Use this option when your Excel version supports XLOOKUP and you want a compact formula that can return the matching response row.
Return all matching outputs with FILTER
Use FILTER when one criteria combination can have multiple valid matching rows that should all be displayed.
Build a Four-Criteria Response Lookup in WPS Spreadsheet
WPS Spreadsheet lets you create drop-down lists, organize a multi-criteria lookup table, and return matching response outputs with INDEX, MATCH, XLOOKUP, or FILTER formulas. It provides a familiar spreadsheet workflow for building and maintaining rule-based response tools.
- 1. Create the criteria lists: In WPS Spreadsheet, place the allowed values for each category in worksheet ranges.
- 2. Add drop-down selectors: Select an input cell and use Data > Data Validation > List to choose the range for that criteria list. Repeat for all four inputs.
- 3. Build the response mapping table: Create a table containing the four criteria columns and the 16 response columns, with one row for each valid combination.
- 4. Add the lookup formula: In the first output cell, use INDEX and MATCH with the four criteria selections to locate the matching table row and return its response.
- 5. Copy formulas across the output area: Copy the formula across the response cells, adjusting the returned response column as needed.
Frequently Asked Questions
How do I make four drop-down lists in Excel?
Select each input cell, go to Data > Data Validation, choose List under Allow, and set Source to the cell range containing the options for that criterion.
Can one formula return all 16 response columns at once?
Yes. If the 16 response columns are adjacent and your Excel version supports XLOOKUP, use XLOOKUP with the response block as the return array. FILTER can also return the complete matching response row.
Why is my multi-criteria INDEX and MATCH formula returning a blank cell?
Check that each selected drop-down value exactly matches the corresponding lookup-table value, including spaces and data type. Also verify that all criteria and response ranges start and end on the same rows.
Should I use nested IF statements for many criteria combinations?
A structured lookup table is usually better. It is easier to review, update, and expand when criteria combinations or response outputs change.
What happens if more than one row matches the four criteria?
INDEX and MATCH returns the first matching row. Use FILTER if you need to display every row that matches the selected criteria.
