This is an example where the first dropdown list is the column headers of a table, and depending on the what the user selects, that will define the contents of the second dropdown list.
The list can be text, numbers, or a combination of both.
Setting up the first dropdown list
There are three column headers that we want to use as the first drop down list.
To add the dropdown list, go to Data > Data Validation.
In the Data Validation window, define both validation criteria.
Under Allow, select List.
Do a direct cell reference to the column headers (=$A$4:$C$4) as the Source.
Featured Course
Black Belt Excel Package
Setting up the second dropdown list
In the same way, go to Data Validation under the Data tab and select List.
However, unlike the first dropdown list, a direct cell reference can’t be used for the second list since the source will be different depending on the selected option in the first list.
- Productivity Div: $A$5:$A$19
- Game Div: $B$5:$B$19
- Utility Div: $C$5:$C$19
Instead, a combination of Excel’s INDEX() and MATCH() function will be used.
However, writing the formula directly on Source box of the Data Validation window will not give you the Excel help to assist you with the syntax.
One option is to write the formula in a random cell in the spreadsheet and then copy and paste that as the Source in the Data Validation window.
The syntax of INDEX() is:
=INDEX(array, row_num, [column_num])- array: The area where the answer could be. In this case it is $A$5:$C$19.
- row_num: This dictates how many rows to move down the array in order to find the answer. Since we want to include all the rows, leave this blank.
- column_num: Indicates what column number the answer list is found in. Since we want this to be dynamic according to the selection in the first dropdown list, the MATCH() function will be used. This will return a number to dictate how many columns to move to the right to find the answer.
The syntax of MATCH() is:
=MATCH(lookup_value, lookup_array, [match_type])- lookup_value: The value you want to look for. In this case, it is $G$4.
- lookup_array: This is where to look for the lookup_value. $A$4:$C$4.
- match_type: There are three options, “1- Less than”, “0 – Exact match”, and “-1 – Greater than”. Use 0.
The final formula is:
=INDEX($A$5:$C$19,,MATCH($G$4,$A$4:$C$4,0))Since this will be pasted to Data Validation, make sure to fix the cell references by adding $ or pressing the F4 key. (Here is a link to other INDEX MATCH guides.)
When you hit ENTER, the value will not show the full list result of the formula, but instead only display the contents of one of the cells.
To check if it returns the correct list, go to the formula bar and hit the F9 key.
It will then show the contents.
Press ESC or CTRL Z to leave this view and revert back to the normal formula.
Once you get the results needed, highlight the entire formula, right click and select Copy (or press CTRL C).
Go to the cell where you want the second dropdown list, cell G5.
Go to Data Validation, select List, and in Source, paste into the Data Validation list.
Click OK and you will see that the second dropdown list changes according to the chosen option for the first dropdown.
Display the list on separate cells
Using the same technique, there is also an option to display the list in separate cells.
Create the first dropdown list using the same method above or copy cell G4 to cell I4.
The INDEX-MATCH approach can be used to display the list.
In Cell I5, you want to display the contents of the first row, depending on the selected Div group in cell I5.
Since you want the list values in separate cells, the array to be used should not be that of the entire table.
Instead, the array should reference to the specific row.
Cell I5 = INDEX(A5:C5,,MATCH($I$4,$A$4:$C$5,0))The array doesn’t need to be fixed with the $ symbol since you want the array to move down when the formula is dragged down to the last row.
The row_num argument is ignored since there is only one row.
If you get a “0” in empty cells, you can hide them with an IF() formula or use custom formatting “#;-#;”
In cases like this where there is only one row in the array, you can skip the row_num argument entirely.
Excel will automatically understand it as blank and consider the next argument to be the column_num.
Cell I5 = INDEX(A5:C5,MATCH($I$4,$A$4:$C$5,0))Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
