Update cell values ​​based on dropdown in google spreadsheet

Question

I have 2 data sheets.

In sheet 1, I have a database of sorted foods (column A) and their contents in the following columns (proteins (B), carbohydrates (C), fats (D), calories (E))

In sheet 2, I created a data validation rule in column A, the criteria is a “list out of range” and looks in the first column in Sheet 1 to create a drop-down list with the selected food.

It's all good and dandy. But when I select any product from the drop-down list on sheet 2, I would like the corresponding cells to be updated with the corresponding nutritional data from sheet 1.

I tried to find a way to find the address of the cell to which the data check list belongs, and thus be able to use OFFSET or a similar function to search for the corresponding cell data. It seems the address of the ADDRESS function gives me the address of the selected cell, not the address that the selected cell refers to.

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

Answer 1

Assuming your columns are labeled try in B2:

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

copied in different directions.

Answer 2

1- Select the entire column.

2- Right-click on the column heading (B in our case), then click on “Data Validation”. Go to "Criteria", select the first option, then click the value field (highlighted).

3 Go to the sheet in which you entered your data and select the entire column.

4- Click ok, then save and release.