Google-sheets – How to add values from one sheet based on a dropdown that references the other

google sheetsvlookup

I have 2 sheets with the same columns with integer values. In sheet A I have a drop down list of the names in sheet B. Based on what drop down I have selected, I'd like to add the values of sheet B to sheet A.

For example:

Sheet A has John with values 5,6,-3 in each column. In Sheet B has Baker with values -1,9,4 and Police with 8,3,-2. If the drop down in A has Baker, I want values in Johns row on A be 4,15,1, if its Police then it should be 13,9,-5, etc.

Every person can select a different job, they are not restricted to just using one selection for everyone. After that is done I'd like to add data validation so John can only pick certain jobs, i.e. prevent him from selecting a value in B called Pilot with it's own set of numbers.

I figure I would have to use VLOOKUP but I'm unsure how to integrate it into a whole row function with SUM and a drop down.

Sheet here

Best Answer

=ArrayFormula(iferror(vlookup(K2:K35,'Class Growth'!$A$2:$J$48,{2,3,4,5,6,7,8,9,10},FALSE),""))

Some notes - working from the outside in:

  • ArrayFormula - to automatically apply the formula to multiple rows.
  • iferror - where a user doesn't have a "Class", vlookup will display #N/A - which is neither attractive nor desirable. In this formula, iferror enables one to set a specific value to display in the case of an error, and in the case the value is "" - which displays as a blank cell; obviously you could change this value to anything meaningful.

  • The documentation for vlookup says simply :

    • VLOOKUP(search_key, range, index, [is_sorted]). In this answer some of these attributes are exploited:
    • if the search_key is "K2", then arrayformula would copy the results down to the bottom of the sheet. However by specifying the full range (K2:K35), the results stop at row 35.
    • index is normally a single integer, but there is an undocumented ability to display many index values and these are described as an array (note: the curly brackets). This enables the values from an entire row to be selectively displayed.
Related Topic