auto populate cell when selected drop down item chosen

**palmer19** · 06-12-2010, 02:44 PM

ive got setup, an excel spreadsheet using a few sheets in the first sheet i have a list of products and prices, and in the sheet 2 i have the product in a drop down from sheet1. i was wondering if there was a way i could auto populate the cell near the item for when a product is chosen the cell beside would bring up price of the item.

Thanks For your help

Josh

**JBeaucaire** · 06-12-2010, 02:49 PM

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

Check the first file in that listing, the OneRow sheet.
Check the second file in that listing, the Main sheet.

INDEX/MATCH explanation
http://www.excelforum.com/excel-gene...ame-row.html#3

**palmer19** · 06-12-2010, 04:41 PM

that i don't think is what im after or if it is it don't work.

in sheet one i have a list of products (food items with prices)
in sheet two there is going to be a recipe thing to work out prices to make each item. and in sheet two to make it easier i have entered all the products into a drop down list that reads from sheet one and displays in the drop down on sheet two. And what i am after is say they choose cheese it would look at sheet one automatically and populate the cell near the drop down box with the given price if some one could possibly help by writing me out the data that would be great i have

the product list in colum A on sheet 1 with the prices on F
on sheet 2 i have the drop down box on a1 and would like the price to appear in b1 when the item is selected

thanks

**palmer19** · 06-12-2010, 05:05 PM

heres the file for some help if it makes it easier

**JBeaucaire** · 06-12-2010, 05:24 PM

This is exactly what I showed. The Main sheet in the second file from the list I gave you demonstrates this exactly.

I added another named range called FoodTable covering your entire database and then used a standard VLOOKUP() formula. Read up on VLOOKUP() so you can fully understand each of the settings.

**palmer19** · 06-13-2010, 05:42 AM

Originally Posted by JBeaucaire

This is exactly what I showed. The Main sheet in the second file from the list I gave you demonstrates this exactly.

I added another named range called FoodTable covering your entire database and then used a standard VLOOKUP() formula. Read up on VLOOKUP() so you can fully understand each of the settings.

Thanks For That JBeaucaire Worked Perfectly