# Returning a Table After Selecting a Value in Drop Down List

#### rtuttle123

##### New Member
Hi,

I was looking to see if anyone knew a way to return a table of information by selecting single value from a drop down list. My issue is that the values in the table will be different with each single value so I am not sure how vlookup could work with that. Think of selecting one value from a drop down and returning a recipe with a list of ingredients in one column and the amount in the next column. I would appreciate any help.

Thanks,

Robert

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Can you post some sample data?

 123456 APPLE PIE RECIPE INGREDIENT QUANTITY UNIT OF MEASURE PIE CRUST 1 BOX APPLES 6 CUPS SUGAR .75 CUPS FLOUR 2 TBSP CINNAMON .75 TSP SALT .25 TSP NUTMEG .125 TSP LEMON JUICE 1 TBSP

<tbody>
</tbody>

I would like to know if there is a way to store info in a table like this on one sheet and have a list on another sheet with all the numbers (123456) that when I select the number (123456) the corresponding data will be displayed in next to the drop down selection. I know you can do this with vlookups and it will return one or multiple results in a row but when you try to get it to look at the same number in different rows it doesn't look to the next row. I wasn't sure if there was a way or a formula that would return a table to a corresponding value like (123456). My hope would be to store multiple tables on a single sheet to draw from. Any help would be appreciated.

Thanks,

Robert

Hi,

Note how the recipe number is copied for each row you want to return, copy this formula across and then down.

Expand the ranges to suit your data. If you get zero in the table as per below, you can turn these off with

File Tab/Options/Advanced/Display Options for this worksheet/un-tick Show a zero in cells that have zero value.

Book1
ABCD
1123456APPLE PIE RECIPE
2123456INGREDIENTQUANTITYUNIT OF MEASURE
3123456PIE CRUST1BOX
4123456APPLES6CUPS
5123456SUGAR0.75CUPS
6123456FLOUR2TBSP
7123456CINNAMON0.75TSP
8123456SALT0.25TSP
9123456NUTMEG0.125TSP
10123456LEMON JUICE1TBSP
Sheet1

Book1
ABC
2Recipe
3123456
4
5INGREDIENTQUANTITYUNIT OF MEASURE
6APPLE PIE RECIPE00
7INGREDIENTQUANTITYUNIT OF MEASURE
8PIE CRUST1BOX
9APPLES6CUPS
10SUGAR0.75CUPS
11FLOUR2TBSP
12CINNAMON0.75TSP
13SALT0.25TSP
14NUTMEG0.125TSP
15LEMON JUICE1TBSP
Sheet2
Cell Formulas
RangeFormula
A6{=IF(ROWS(Sheet2!\$A\$6:A6)>COUNTIF(Sheet1!\$A\$1:\$A\$10,Sheet2!\$A\$3),"",INDEX(Sheet1!B\$1:B\$10,SMALL(IF(Sheet1!\$A\$1:\$A\$10=Sheet2!\$A\$3,ROW(Sheet1!A\$1:A\$10)-ROW(Sheet1!A\$1)+1),ROWS(Sheet2!\$A\$6:A6))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks so much RasGhul. This formula worked perfectly. I appreciate the help.

You're welcome thanks for the feedback.

Replies
1
Views
153
Replies
6
Views
197
Replies
4
Views
179
Replies
3
Views
444
Replies
3
Views
278

1,203,380
Messages
6,055,099
Members
444,762
Latest member
MRC3411

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back