Adding a Drop Down Menu with Data

April 1, 2009 at 15:30:52
Specs: Windows 2000
I currently have a drop down menu in excel; however, I dont know how to include extra data with the selected item from the drop down menu. For example if I have a drop down menu that has three options, jeans, sweater, and t-shirt. I have a number that goes with each item (100, 50, or 25 respectively). When I select jeans from the drop down menu, i need the value 100 to appear in the cell next to the drop down menu. Can someone please help me figure out how to do this?

See More: Adding a Drop Down Menu with Data

Report •

April 1, 2009 at 18:52:36
Is the data (e.g. 100) static or variable?

If it's static, you can use an IF statement:

Let's say your dropdown is in E1. In E2, enter:

=IF(E1="jeans",100,IF(E1="sweater",50,IF(E1= etc.)))

However, I'm guessing your list is more extensive than 3 items and that the data may change, so I doubt you want to hardcode the formula.

I'd set up a table like:

     A         B
1  jeans      100
2  sweater     50
3  t-shirt     25

and then use VLOOKUP:


Obviously you'll want to populate the dropdown directly from the range where the items are to ensure an exact match for the VLOOKUP function.

Report •

April 2, 2009 at 10:58:05
The VLOOKUP worked great! thank you! (yes, I have about 40 items, which is why I didnt want to hard copy them in, sorry i didnt mention that originally).

The only other question I have is how can I use the VLOOKUP function is my list is on a separate worksheet than the drop-down list? (They are in the same file)

Also, is it possible to do something like this from a separate file?

Report •

April 2, 2009 at 12:29:38
For a separate sheet use:


For a separate file use something like:

=VLOOKUP(E1,'C:\Documents and Settings\user\Desktop\[Book2.xls]Sheet2'!$A$1:$B$3,2,0)

If the other book (e.g. Book2) is open when you create the formula, the formula should update and add the full path once you save the file.

Report •

Related Solutions

Ask Question