## Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do a lookup in a dataset or preferably in an Excel defined Table. In other words, this will auto populate other cells when selecting values in a drop-down list.

I will also demonstrate how to create, edit and add values to a drop-down list. There are several kinds of drop-down lists in Excel. Data validation and Combo Boxes. They have their advantages and disadvantages and I will discuss them in this article.

### Data validation drop-down list

The Data Validation drop-down list is easy to set up but it has its flaws, the purpose with data validation is to force the user to select one out of several predetermined values.

This can, however, be easily ignored by the user simply by pasting a value to the drop-down list.

Another disadvantage is that the data validation drop-down list won't allow you to search for a value in the list, this can make it time-consuming and ineffective to use if it contains lots of values.

I recommend using a combo box if you want to be able to search a drop-down list. A combo box is also a drop-down list that you can easily create and manipulate.

#### Create a drop-down list

The data source is in this case an Excel defined Table which has its advantages. To be able to use it in a drop-down list a workaround is needed, the INDIRECT function makes it possible to reference the Table as a source.

- Select cell B3.
- Go to "Data" tab on the ribbon.
- Click "Data Validation" button

- Select List

- Type =INDIRECT("Table1[Item]") in "Source:" window
- Click OK

### Formula that will auto populate a single cell

The first formula I will demonstrate is a simple INDEX - MATCH formula that will use the selected value in the drop-down list to search a dataset and return the adjacent value if a match is found.

Formula in cell C3:

The MATCH function returns the relative position of the drop-down list value in column Table1[Item] if found.

MATCH(Sheet1!$B3, Table1[Item],0)

becomes

MATCH("80X", {"200X"; "120X"; "80X"; "70X"; "150X"; "100X"; "130X"; "110X"; "140X"; "190X"; "90X"; "170X"; "180X"; "160X"; "200X"; "120X"; "80X"; "150X"; "100X"; "130X"; "110X"; "90X"; "170X"; "180X"}, 0)

and returns 3. "80X" is found as the third value in the array.

The INDEX function returns the value in column Table1[Color] based on the relative position returned from the MATCH function.

INDEX(Table1[Color], MATCH(Sheet1!$B3, Table1[Item], 0))

becomes

INDEX(Table1[Color], 3)

becomes

INDEX({"Navy"; "Lime"; "White"; "Yellow"; "Magenta / Fuchsia"; "Gray"; "Lime"; "Cyan / Aqua"; "Purple"; "Black"; "Teal"; "Magenta / Fuchsia"; "White"; "Teal"; "Teal"; "White"; "Magenta / Fuchsia"; "Black"; "Teal"; "Gray"; "Yellow"; "Purple"; "Lime"; "Cyan / Aqua"}, 3)

and returns "White in cell C3.

### Formula that will auto populate several cells

The following formula is more complicated, it returns multiple values distributed horizontally starting from column C.

Array formula in cell C3:

To enter an array formula, type the formula in cell C3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

#### Explaining formula in cell C3

**Step 1 - Find cells that equal condition**

Sheet2!$B3=Table1[Item] is a logical expression that returns boolean values, TRUE or FALSE.

Sheet2!$B3=Table1[Item]

becomes

"80X"={"200X"; "120X"; "80X"; "70X"; "150X"; "100X"; "130X"; "110X"; "140X"; "190X"; "90X"; "170X"; "180X"; "160X"; "200X"; "120X"; "80X"; "150X"; "100X"; "130X"; "110X"; "90X"; "170X"; "180X"}

and returns this array:

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

**Step 2 - Calculate row numbers of matching cells**

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

The function replaces TRUE with the corresponding relative row number and FALSE with nothing "".

IF(Sheet2!$B3=Table1[Item], MATCH(ROW(Table1[Item]), ROW(Table1[Item])), "")

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, MATCH(ROW(Table1[Item]), ROW(Table1[Item])), "")

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24}, "")

and returns

{""; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 17; ""; ""; ""; ""; ""; ""; ""}.

*Step 3 - Extract k-th smallest row number*

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

The COLUMNS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF(Sheet2!$B3=Table1[Item], MATCH(ROW(Table1[Item]), ROW(Table1[Item])), ""), COLUMNS($A$1:A1))

becomes

SMALL({""; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 17; ""; ""; ""; ""; ""; ""; ""}, COLUMNS($A$1:A1))

becomes

SMALL({""; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 17; ""; ""; ""; ""; ""; ""; ""}, 1)

and returns 3.

**Step 4 - Return value from Excel defined Table**

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX(Table1[Color], SMALL(IF(Sheet2!$B3=Table1[Item], MATCH(ROW(Table1[Item]), ROW(Table1[Item])), ""), COLUMNS($A$1:A1)))

becomes

INDEX(Table1[Color], 3)

and returns "White" in cell C3.

**Step 5 - Return blank if formula returns error**

IFERROR(INDEX(Table1[Color], SMALL(IF(Sheet2!$B3=Table1[Item], MATCH(ROW(Table1[Item]), ROW(Table1[Item])), ""), COLUMNS($A$1:A1))), "")

The IFERROR handles all errors in a formula, it lets you specify a value to return if an error is found.

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Vlookup across multiple sheets

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

VLOOKUP and return multiple values across columns

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

### 12 Responses to “Use a drop down list to search and return multiple values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

thank you so much!!! Worked Perfectly

For some reason I am unable to 'ask a question' by clicking the link, so, I thought i'll post it here since it is relevant to drop-down list.

In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a table, tblCustomer, where I add new customers. Similarly, in the Vendor sheet I have a table, tblVendor, where I add new vendors. In the Payment sheet I have a table, tblPayment, where i have three columns; Date, Amount and Name. Now, here is what I want to do; In the Name column of the tblPayment, I want to create a drop down list in each cell, which would contain all the names from tblCustomer[Name] and tblVendor[Name]. This way I can fill in the Date, Amount and then select one of all the names available in the drop down list of my Name cell. Is this possible without using VB code or any macro? If so, please help me out with this.

Rattan,

Read this post: Basic invoice template in excel

Pls the best way excel functions espeacially VLOOKUP / HLOOKUP etc to use in various way , columns, functions, multiple way.

rgds

abdussamed

Hi, I know this isn't array formula but maybe you can still help me. I have a spreadsheet that I use for 3 different companies.

What i would really like to do is have a drop down menu with the three company names: eg: Mcdonalds, Pizza Hut, Subway and then when i choose which company the spreadsheet will be for then all the contact information and logo will appear as a header on the top of the spread sheet. is this possible?

Hi one more question this array formula that you gave me earlier in the year, it's not working now.

I added another drop down box right beside the first one and i just want to take the exact same informaion so i dragged the formula to copy into the other cells and i just changed from B26 to B27 where the drop down menu is to look up.

Now it is not getting any of my informaion even though it's the exact same formula

Ainslie,

Can you provide both formulas? I am not sure whats wrong.

This is the original:

=IFERROR(INDEX(Panels!$C$2:$C$80, SMALL(IF($B$56=Panels!$A$2:$A$80, MATCH(ROW(Panels!$A$2:$A$80), ROW(Panels!$A$2:$A$80)), ""), ROW(A1))), "")

This is the one i'm trying to get to work. I need it to do the exact same thing in cells below where the original is used but both need to do the same thing just the drop down box where it has the information where to lookup is in a different location

=IFERROR(INDEX(Panels!$C$2:$C$80, SMALL(IF($B$57=Panels!$A$2:$A$80, MATCH(ROW(Panels!$A$2:$A$80), ROW(Panels!$A$2:$A$80)), ""), ROW(A1))), "")

Ainslie,

There seems to be nothing wrong with the formulas. Maybe the value ($B$57) in the drop down list doesnt exactly match some of the values in your list (Panels!$A$2:$A$80)?

nope, that's all the same, nothing has changed. it works in the other drop down box in B56 - the exact same values but not in B57

Aynsley Wall,

See this post: Use a drop down list to select company info in header (vba)

[...] me all the cars in my table that has the blue colour assigned to them. Similar to the code here Use a drop down list to search and return multiple values | Get Digital Help - Microsoft Excel resou... If i just had the one list box then fine, But I want many rows of list boxes that do the same [...]