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

Company 1

10

45

5

6

6

23

<tbody>
</tbody>

	Company 1	Company 2	Company 3	Company 4	Company 5	Company 6	Company 7
Product 1	10	4	3	6	4	0	4
Product 3	45	4	3	4	5	7	6
Product 4	5	3	3	5	5	5	4
Product 5	6	3	4	6	33	76	7
Product6	6	32	7	6	7	44	4
Product 7	23	23		7	23	4	3

<tbody>
</tbody>

 

[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.