auto populate a column from another table based on the value from a drop down list

[moeman]

how can i auto populate a column with values from another table based on the value from a drop down list as a reference to the table.
So based on the value/company i select from the drop down list it should auto populate the corresponding product qtys from table 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company 1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]45[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Company 1[/TD]
[TD]Company 2[/TD]
[TD]Company 3[/TD]
[TD]Company 4[/TD]
[TD]Company 5[/TD]
[TD]Company 6[/TD]
[TD]Company 7[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]45[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]33[/TD]
[TD]76[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Product6[/TD]
[TD]6[/TD]
[TD]32[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]44[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product 7[/TD]
[TD]23[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]23[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[sunny102]

Use the formula below

=INDEX(Sheet1!$B$2:$H$100,MATCH($A2,Sheet1!$A$1:$A$100,0),MATCH($B$1,Sheet1!$B$1:$H$1,0))
it assumes that your orignial data is in shet1 and drop down is in cell B1 of sheet in which you want the values and it list all products in Column A from cell A2 downwards

 

[moeman]

Thank you! i'm having trouble applying the suggested solution, would you please give more details.

 

[sunny102]

Use the following sheet

https://drive.google.com/open?id=0Bx9ubBZFFEejRlhjdjVuVUF6cnM

Thank you! i'm having trouble applying the suggested solution, would you please give more details.