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

  • 1
    Welcome to Webapps. Please share a sample sheet including desired result. Aug 11, 2019 at 23:33
  • I'm not sure what do you mean by including a sample sheet. Idk how to get the result I want which is why I'm asking. Aug 12, 2019 at 4:14
  • "... what do you mean by including a sample sheet. Idk how to get the result I want which is why I'm asking." Something like this or this Aug 13, 2019 at 12:23

1 Answer 1


=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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.