Excel 2007 : Getting price to autofill after picking product from dropdown list

[Luis Honorato]

Hi everyone, I am new to the forum and excel so first off, I wanted to apologize for any errors that may occur.
I am trying to get a price to show up in cell after picking product from dropdown list. The product and price table are in another sheet and are seperated product price - product price, and I don´t know VBA at all if that is necessary.

The example of the Invoice sheet1 product table sheet2:

Sheet1
A------------------------------------------B-----------------------------------------------------------C
Product Tipe--------------------------Product--------------------------------------------------------Price
Diverse----------------------------------Washer--------------------------------------------------------0,30


Sheet2
A-----------B---------------C------------D----------------E--------------F----------G----------------H
Diverse----Price--------Steel-------Price-----------Aluminum--------price-------Nickel-------------Price
washer---------0,30---------1/2--------2.00------------1/4 pipe---------7.00--------sheet-------------2.00

Hope someone can help.

Thanks

[Fotis1991]

Hi and welcome to the forum

You can do this, using INDEX&MATCH functions.

=INDEX('PRICE LIST'!B2:B6;MATCH(A2;List;0))

Hope to helps you.

[sukman]

Hello Fren
Thanks for the answer, However the problem has arosed in my case dont know what exactly it is. I cud not figure out all i knew is it could be due to some of the addons.
I made a same Price List and Dropdon list aswel. and i also applied the index and match formula aswel but the answer i get is N/A# WHAT DOES IT MEAN. Plz help me out

[Luis Honorato]

Thanks for replying Fotis1991 but that is not exactly what I need, See the products and prices are in columns product-Diverse-price etc. The problem is that I can get one product and price to work fine, the thing is I have various 4 products columns and 4 price columns. If there was a way to extend formula to involve the rest that would be great.

[Fotis1991]

..In this case, i think that you must upload a sample workbook.

It will better for us to understand, what are your needs...

[Luis Honorato]

Myprob.xls The problem is on the second sheet "IVA" with a small explanation.
Hope you can help, and thank you

[Fotis1991]

Hi

Put this formula in J12.

=IF(A12="Div";INDEX(PeçasMaterial!$B$2:$B$1000;MATCH(B12;PeçasMaterial!$A$2:$A$1000;0));IF(A12="Inox";INDEX(PeçasMaterial!$D$2:$D$1000;MATCH(B12;PeçasMaterial!$C$2:$C$1000;0)))+IF(A12="PVC";INDEX(PeçasMaterial!$H$2:$H$1000;MATCH(B12;PeçasMaterial!$G$2:$G$1000;0))))+IF(A12=$E$6;INDEX(PeçasMaterial!$F$2:$F$1000;MATCH(B12;PeçasMaterial!$E$2:$E$1000;0)))

I Could't type this word,"Latão", so i used referance cell E6!

Hope to helps you.

[Luis Honorato]

Thank you very much Fotis1991, it´s done, couldn´t have done it without your help, thanks again.
Now how do I mark this as Solved?

[Fotis1991]

You are welcome!

Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

[Fotis1991]

Ηι

This is a new question. So please open a new(your own) Thread, and be sure that will find your answer.