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

*Article last updated on August 29, 2017*

I then need to be able to look up whatever option is picked in B50 and look up mulitple values from a different worksheet entiled "Panels" the Panels table goes from A1:F80

So if i choose the option 140XL in B50 below it I need all the information from the "panels" sheet that corresponds with 140XL.The Panels Sheet information is set up like this:

140XL 12025 16" Large

140XL 58625 12" Small

140XL 99951 12" Rear

So i need it to look up 140XL and get the infromation 12025,58625,9951

i have looked all over the internet for help and it's either way too confusing or doesn't deal with multiple worksheets. I know it is possible but i really am out of ideas

**Answer:**

**Create drop down list**

- Go to "Data" tab
- Click "Data Validation" button

- Select List

- Type 140XL, 150XL, 160XL in "Source:" window
- Click OK

Recommended reading

Add new items to a drop down list automatically

A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you […]

**Excel 2007 array formula in cell B5:**

Learn to construct a formula that returns multiple values sorted alphabetically:

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

**How to enter an array formula**

- Copy (Ctrl + c) above array formula
- Select cell B5
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

Array formulas allows you to do advanced calculations not possible with regular formulas.

**How to copy array formula**

- Select cell B5
- Copy (Ctrl + c)
- Select cell range B7:B16
- Paste (Ctrl + v)

**How this formula works**

You can find an explanation here:

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.

**Download excel *.xlsx file**

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 […]

Create a drop down list containing only unique distinct alphabetically sorted text values

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 a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Dependent data validation lists in multiple rows

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent […]

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 – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

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 blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]

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

Use the img tag, like this: <img src="Insert pic link here">

**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 [...]