Data Lists

[Doogie2000]

In one spreadsheet I have two data columns one with a product type and one with the price.
In another spreadsheet I have the product types in a drop down list. What I want it to do is once the product is selected to have the price automatically pop into the next cell. I was originall doing something like:
=IF( A1="FOAM", $0.50,IF(A1="GLASS", $0.75," ")).
But then I discovered you could only have a maximum of 7 inset functions. Is there another way to do this in Excel 2003?

[Paul]

Hi Doogie, and welcome to the forum.

Since you have a table of products and prices to lookup from, take a look at the VLOOKUP function in Excel Help (or the myriad of examples on this forum).

Hope that helps you in the right direction!

[JBeaucaire]

My recommendation is INDEX/MATCH since it works in all places a VLOOKUPwould work and a bunch more where it would not. Once you get INDEX/MATCH working in your head, you'll use it for everything...it replaces VLOOKUP, HLOOKUP and LOOKUP in most cases.

Here's some posts regarding its use:

http://www.excelforum.com/excel-gene...ame-row.html#3
http://www.excelforum.com/attachment...tch-sample.xls


=INDEX(Prices,MATCH(CodeChosen,Codes,0))

Prices - the range of prices on the other sheet
CodeChosen - your drop down selection
Codes - range of codes on other sheet.

[Doogie2000]

Excellent. Its working perfectly now, thanks for the help.

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]