1

I have a number of spreadsheets

enter image description here

I want to join them horizontally by Id.

enter image description here

How would you achieve that?

p.s I should mention that the id order could be different in each spreadsheet

1

1 Answer 1

1

Use the vlookup() function in an { array expression }. Put this formula in cell Sheet1!C1:

=arrayformula( 
  { 
    iferror( vlookup(A1:A, Sheet2!A1:C, column(Sheet2!B1:C), false) ), 
    iferror( vlookup(A1:A, Sheet3!A1:B, column(Sheet2!B1), false) ) 
  } 
)

If you want a new table in the Combine sheet, put this formula in cell Combine!A1:

=arrayformula( 
  { 
    Sheet1!A1:B, 
    iferror( vlookup(Sheet1!A1:A, Sheet2!A1:C, column(Sheet2!B1:C), false) ), 
    iferror( vlookup(Sheet1!A1:A, Sheet3!A1:B, column(Sheet2!B1), false) ) 
  } 
)
5
  • is it possible to pick a specific columns? Oct 9, 2022 at 3:21
  • Merge all columns in the Combine sheet, then use query() or filter() to create reports in yet another sheet. Please ask only one question per post. Oct 9, 2022 at 5:17
  • fyi, instead of specifying column(Sheet2!B1:C) I used {2,3} do define columns that I want. Oct 9, 2022 at 6:25
  • Yes... hard coding the column numbers gives the same result as getting the column numbers from the range. Remember to update your hard-coded column numbers if you later insert columns. Oct 9, 2022 at 6:59
  • right, however using column() method will update them automatically if they change? right? Oct 9, 2022 at 12:37

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.