Google-sheets – Change value of a cell based on a drop menu of values on a different sheet

google sheets

I want cells in a column, to have drop down menus. Then, the rest of the row needs to match the corresponding column set.

Here's an example that explains what I want:

Sheet 1:
A1, a drop down menu of the values from sheet 2 column A
B2, the corresponding value from Sheet 2 Column B

Best Answer

I will guide you to the solution:

First Step

is to set the drop down menu's (combo box) on the range you want. In my example it will be in sheet1, having range A1:A10. From menu Data, choose this option:

enter image description here

Set the criteria range, to be the range in sheet2 (values in combo box):

enter image description here

Now the values will appear in the first sheet:

enter image description here

Second Step

is to add the following formula in cell B1 of sheet1: 

=ARRAYFORMULA(IF(ISBLANK(A:A)=TRUE,"";VLOOKUP(A:A,Sheet2!A:B,
  {2} * SIGN(ROW(A:A)),0)))

This will automatically populate column B, with the values found in column B of sheet2, corresponding to the input given in column A of sheet1.

See the example file I've prepared: COMBOBOX and VLOOKUP

Related Topic