Auto-populate cell based on values in other cells

I am using Excel 2003.

I need help auto populating cells depending on what previous cells are showing.

In Column A, I have a drop down list with a series of products.

In Column B, I have a drop down list with two options, relating to those products in Column A.

Column C is my Quantity column, and that value would be inputted manually.

Column D is my Price Per column, and I would like that to auto populate, based on what is showing in Column A and Column B. So for example, if Column A is showing Product ABC and Column B is showing Option 1, it would be a different price than if Product ABC was paired with Option 2 in Column B.

Column E would be total amt, just a simple price per x qty.

I have absolutely no idea how to set up the relationship between Columns A and B, and Column D.

Any help would be much appreciated!

-Cyndi

I would set up a table with your products in column A of a different sheet in your workbook and prices for Option 1 in B and Op 2 in C. Then I would use the INDEX and MATCh functions to find the proper values. As per your example, the formula in column D copied down would be

=INDEX(Sheet2!$B$2:$C$11,MATCH(A2,Products,0),MATCH(B2,Sheet2!$B$1:$C$1,0))

where "Products" is a named range referrring to sheet2!A2:A20. Take a look at the attachment and let us know if you have any questions.

ChemistB

This worked perfectly, thank you!

Glad it worked.

ChemistB

Yes, on the Test tab when I select a product from the drop down list it will only return the 1st products values?

Yes, how do I get it over to you to look at?