Creating an in-cell drop-down menu from an array is not rocket science. In Google Sheets, you can use Data Validation for this. But what about creating a drop-down menu from multiple ranges (arrays)?

It depends on the values to include in the menu arranged in your Sheet. Also, it depends on whether the values are ‘static’, I mean no changes (modification or addition) will occur in the future, or a growing list of values.

I am considering all the above scenarios in my examples. See the examples below to understand how to create a drop-down menu from multiple arrays in Google Sheets.

Examples to In-Line Drop Down Menu from Multiple Ranges in Sheets

To create an in-line drop-down menu, you can approach two methods. Both are using data validation. First of all, understand those basic methods.

In column C2:C11 I have the numbers 1 to 10. In cell A2 (or any other cell), I can create a drop-down menu with the above numbers as the menu items.

Approach 1:

Click on cell A2 and then go to the menu Data > Data Validation.

In that make sure that the “Criteria” are “List from range“. Select the range C2:C11 and your in-cell drop-down menu are ready! There is a second method, i.e. List of items.

Approach 2:

In this against the “Criteria” in data validation select “List of items” and manually enter your values in C2:C11 as comma separated. Here I am going to share a cool tip.

If the values to type in the “List of items” is already in your Sheet (here we have it in C2:C11), you can use the below tip.

You can use the below TEXTJOIN formula in a blank cell to generate a comma-separated list to use in data validation.

=textjoin(",",TRUE,C2:C11)

It will convert the numbers in the array C2:C11 as a comma separated. Copy that numbers and paste it in the above field in data validation. It’s time-saving, right?

Now to the topic, i.e. how to create an in-cell drop-down menu from multiple ranges in Google Sheets. For this, I am going to use the above two approaches. Let me begin with approach 1 above which is more flexible.

Data Validation Drop-Down Range in Two or More Columns

List from Range Approach (Approach 1)

For all the examples, I am using sequential numbers as menu items. This is for my convenient to explain the steps. You can use text strings or dates too.

The range C2:C11 contains the numbers 1 to 10 and E2:E11 contain 11 to 20. Combined, it’s the sequential numbers 1-20. Please note the values are in two remote ranges.

In any spare/blank column combine these ranges using the below formula. I am using cell G2 for this.

={C2:C11;E2:E11}

If the range is growing, then you can include the Filter function as below.

={filter(C2:C,C2:C<>"");filter(E2:E,E2:E<>"")}

Then select G2:G in the data validation “List from range” field. This way you can include a two column list in a single data validation drop-down.

If you have more columns or even that columns are in different range you can use the same method. Just arrange each column one below another, using a formula similar to the one above.

Assume the values to create your drop-down are arranged in multiple adjoining columns in same Sheet or two Sheets within the same file. Then there is a simple formula to arrange the values in a single column to use in the drop-down.

Values in Sheet1 and Sheet2:

Formula and Drop-Down in Sheet3:

=sort(TRANSPOSE(split(TEXTJOIN(",",TRUE,Sheet1!A2:C11)&","&TEXTJOIN(",",TRUE,Sheet2!A2:C11),",")))

List of Items Approach (Approach 2)

Here is another way of creating a drop-down menu from multiple ranges in Google Sheets. If the above values are ‘static’, I mean may not change in the future, you can use the above method also in multiple columns.

I mean just remove the outer SORT, TRANSPOSE, and SPLIT functions in the above formula. Copy the generated comma separated values into data validation “List of items” field.

That’s all. Enjoy!

Related Reading:

1. A Drop-down Menu in Google Sheets to View Content from Any Sheets.
2. How to Get an All Selection Option in a Drop-down in Google Sheets.
3. Populate a Full Month’s Dates Based on a Drop-down in Google Sheets.
4. Create a Drop-Down to Filter Data From Rows and Columns.
5. Multi-Row Dynamic Dependent Drop Down List in Google Sheets.
6. Auto Populate Information Based on Drop down Selection in Google Sheets.
7. Distinct Values in Drop Down List in Google Sheets.
8. Data Validation – How Not to Allow Duplicates in Google Sheets.
9. The Best Data Validation Examples in Google Sheets.