# How to Make Dependent Drop Down List with Spaces in Excel

Letâ€™s say we have 3 Product Lists with two-word names, resulting in spaces among words. We have to generate a drop-down list that allows spaces within dependent names.

## How to Make a Dependent Drop-Down List with Spaces: 2 Easy Ways

While making a dependent drop-down list via the Data Validation feature, spaces within dependent entries cause errors. Luckily, there are a few workarounds.

### Method 1 – A Dependent Drop-Down List with Spaces Using Combined Excel Functions

Combined INDEX and MATCH functions can ignore spaces within dependent names or titles

Drop Down List

Steps:

• Place the cursor in any blank cell (i.e., F4).
• Go to Data.
• Select Data Validation (from Data Tools section).

• The Data Validation window opens up.
• Select Validation Criteria Allow as List.
• Select B4:D4 as Source.
• Click on OK.

• In the worksheet, click on the down arrow icon in cell F4. You’ll see all the assigned cells as Source in the Data Validation window as a drop-down list.

Dependent Drop Down List

• Create a drop-down list in cell F5. Use the following formula as Source in the Data Validation window.
`=INDEX(B5:D13,,MATCH(\$F\$4,\$B\$4:\$D\$4,0))`

Comparing the formula to previously described syntaxes, the formula portion MATCH(\$F\$4,\$B\$4:\$D\$4,0) passes the col_num to the INDEX function. B5:D13=array of the INDEX function. And it takes no row_num.

The MATCH portion takes F4 as lookup_value, B4:D4 as lookup_array and 0 refers to the exact match type.

• Click on OK.

• In cell F5, click on the arrow icon and you’ll get a full list of the results.

Read More: Excel Dependent Drop Down List

### Method 2 – Using Defined Names and INDIRECT Function

Defining Names

• Select the column headers (i.e., B4:D4).
• Go to Formulas.
• Select Define Name (from the Defined Names section).

• The New Name window appears.
• Assign a Name for the cells (i.e., List).
• Excel automatically puts a cell reference for Refers to box.
• Click on OK.

• Assign names to all three product lists.

• You can check all the assigned names by going to Formulas and selecting Name Manager (from the Define Name section).

Drop Down List

• Select the F4 cell.
• Go to Data and select Data Validation (from the Data Tools section).

• The Data Validation dialog box appears.
• Under the Validation Criteria, select List in Allow box.
• Type =List (List is the defined name for column headers) in the Source dialog box.
• Click on OK.

• If you click open the menu on cell F4, you’ll see column headers as options.

Dependent Drop Down List

• Create another Data Validation window below the first one.
• Paste the following formula as the Source.

Â =INDIRECT(SUBSTITUTE(\$F\$4,” “,”_”))

In the formula, the SUBSTITUTE function replaces spaces in the F4 reference with an underscore (_). You can use other characters to replace spaces in defined names and similarly in the INDIRECT formula. The INDIRECT function converts the F4 entry similar to the assigned names. Depending on F4 values, the INDIRECT function displays the product list.

• Click OK.
• You’ll get a dependent drop-down list.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

1. Thank you so much for this. I scoured videos and articles and the few I found explaining how to use spaces in column headers were hard to understand. The dataset template to follow along with was a tremendous help as well. Thank you again.

• Dear Kayla,

Thanks for your appreciation and we are glad that our Dataset Template helped you.

Regards
Shamima | Project Manager | ExcelDemy

Advanced Excel Exercises with Solutions PDF