A simple approach to create dependent lists

Realistic Case: Dependent Drop-Down Lists in Excel with Multiple Words & Spaces in 1st List

This video builds on the methods shown in a previous tutorial which showed you how to create an Excel dynamic drop-down data validation list that was dependent on the selection of another drop-down.

In that video the first list contained numbers whereas in this video we take a look at a more realistic case where the first list contains text with multiple words and spaces.

This approach works if your drop downs are in different worksheets as well.

Excel dynamic data validation many words and spaces

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.

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))

Video and Workbook

Feel free to Download the Workbook HERE.

Free Excel Download

 

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

4 Comments

  1. G April 2, 2019 at 7:33 am - Reply

    when i do this i am left with the #VALUE! error notification. i am assuming that is is because i am leaving the row category empty. would this have an effect?

    • Bryon Smedley April 7, 2019 at 7:22 pm - Reply

      Which step in the tutorial are you performing that is generating the #VALUE! error?

  2. Gemma April 12, 2019 at 2:13 pm - Reply

    so having spent ages working this out, i finally managed to create my dependant drop down. But i need to do it again. I need a 3rd drop down that is dependant on the selection from teh 2nd. The information for the 3rd drop down are in separate tables. What do i do?

    • Bryon Smedley May 5, 2019 at 11:19 am - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      The XelPlus Team

Leave A Comment

Share This