0

I have next array formula that combines columns from multiple sheets:

=arrayformula( 
  { 
    Data1!A2:F, 
    iferror( vlookup(Data1!A2:A, Data2!A2:C, {2,3}, false) ), 
    iferror( vlookup(Data1!A2:A, Data3!A2:E, {4,5}, false) ) 
  } 
)

The Data1!A2:F returns columns A:F from the Data1 sheet, I want to be able to custom pick columns instead. How can I do that?

1

1 Answer 1

0

EDIT: try to select the colums in this way (now they are ALL selected, delete the columns you do not want)

=arrayformula( 
  { 
    query(Data1!A2:F,"select A,B,C,D,E,F"), 
    iferror( vlookup(Data1!A2:A, Data2!A2:C, {2,3}, false) ), 
    iferror( vlookup(Data1!A2:A, Data3!A2:E, {2}, false) ) 
  } 
)

Include your formula in a query, like this that keeps values from columns 1 and 4:

=query(
  arrayformula( 
  { 
    Data1!A2:F, 
    iferror( vlookup(Data1!A2:A, Data2!A2:C, {2,3}, false) ), 
    iferror( vlookup(Data1!A2:A, Data3!A2:E, {4,5}, false) ) 
  } 
 ),
"select Col1,Col4"
)
5
  • I believe it is querying the whole arrayformula result, I want to be able to specify custom columns from the first range Data1!A2:F, Oct 12, 2022 at 5:53
  • Can you attach a reduced copy of your file?
    – Daniele
    Oct 12, 2022 at 6:00
  • here is the one docs.google.com/spreadsheets/d/…. See the formula within the Combine sheet. Oct 12, 2022 at 8:13
  • I want to be able to cherry-pick the columns from the Data1!A2:F - is the first thing within an arrayformula, when adding query and anything like "select Col1,Col4" it works a bit differently Oct 12, 2022 at 8:43
  • I modified my formula
    – Daniele
    Oct 12, 2022 at 11:26

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.