Updating cell values based on selection in drop down list in google spreadsheet

Question

I have 2 sheets of data.

In sheet 1 I have a database of sorted foodstuffs (Column A) and their nutritional content in the following columns (proteins(B), carbohydrates(C), fats(D), calories(E))

In sheet 2 I have created a data validation rule in the column A, the criteria is "list from a range" and looks in the first column in Sheet1 to create a drop down list with the selected foodstuff.

This is all nice and dandy. But when I select any given foodstuff in the drop down list in sheet 2, I would like the corresponding cells to update with relevant nutritional data from sheet 1.

I have tried to find a way to find the address of the cell that data validation list is referring to, and thus be able to use OFFSET or a similar function to find the relevant cell data. It seems the ADDRESS function address only gives me the address of the selected cell, and not the address that the selected cell refers to.

How can I populate the data in sheet 2, with the relevant data from sheet 1, based on the selection in the drop down list?

Answer 1

Assuming your columns are labelled, please try in B2:

=vlookup($A2,Sheet1!$A:$E,column(),0)  

copied across and down to suit.