Drop down cells and looking up related information

Note that we have an updated blog post with the Excel Screenshots for Drop down list then Excel lookup information. Go to that sheet for more information

A visitor wrote


I am in dire need of some excel assistance.  Can you guys help?

I need to create a drop down list in excel – based on a range of info on a previous worksheet.  And in the next cell a cost associated with whichever item is chosen from the drop down list.


On Worksheet 1

A             B

1              Shirt       165

2              Cap        55

3              Wallet   235

4              Skirt       185

On Worksheet 2

A             B


There is a drop down list in Column A relating to the range in Column A on Worksheet 1.  Column B must agree.  So, If A1 is Shirt then B1 is 165, If A1 is Cap then B1 is 55, if A1 is Wallet then B1 is 235, if A1 is Skirt then B1 is 185

Does that make sense?  Can you help?

You need to use the Data Validation tool in Excel, and the VLOOKUP function. Below a YouTube video showing how it works.

The key here is because you have 2 different sheets you are going to have to use Named Ranges to make the Data Validation work. The VLOOKUP will be the standard VLOOKUP commands with no funnies.