Google-sheets – Vlookup with values from another tab

formulasgoogle sheetsgoogle-sheets-arrayformula

Link to my sheet: https://docs.google.com/spreadsheets/d/1E3gH661qnZDUvP48Ubg6epuHwpDH70gANE8wW968dYg/edit?usp=sharing

I'm having trouble understanding what I'm doing wrong.
I'd like to have this spreadsheet autofill data based on the selection from the drop down menu under name. In the examples tab I'd like to be able to select a name from the drop down menu and have it automatically fill in their phone numbers and emails based on the information plugged into the "Contacts" sheet.

From what I understand this can be achieved with an array formula, but I seem to be misunderstanding the application. This is the formula I've gotten to so far, was wondering if someone could outline what I'm doing wrong.

=arrayformula(IF($E4:E8=Contacts!D4:D8, hlookup(Contacts!G4:G16,F4:F16, 1, False)))

Best Answer

Please use the following formula for your needs:

=IFERROR(ArrayFormula(VLOOKUP(E4:E16,Contacts!$D$4:$H$16, {4,5}, False)))

Functions used:

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

Google-sheets – Populate column with editable range based on drop-down

Instead of using drop-downs from data validation and formulas put all the information in a single sheet and use filters or filters views. For details see the official help article Filter your data - Docs editors Help

Best Answer

Related Solutions

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

First Step

Second Step

Google-sheets – Populate column with editable range based on drop-down

Related Topic