I have a number of spreadsheets
I want to join them horizontally by Id.
How would you achieve that?
p.s I should mention that the id order could be different in each spreadsheet
-
here is an input spreadsheet example docs.google.com/spreadsheets/d/…– user2926340Commented Oct 7, 2022 at 11:57
Add a comment
|
1 Answer
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) )
}
)
answered Oct 7, 2022 at 13:43
-
-
Merge all columns in the
Combinesheet, then usequery()orfilter()to create reports in yet another sheet. Please ask only one question per post. Commented Oct 9, 2022 at 5:17 -
fyi, instead of specifying
column(Sheet2!B1:C)I used{2,3}do define columns that I want. Commented 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. Commented Oct 9, 2022 at 6:59
-
right, however using column() method will update them automatically if they change? right? Commented Oct 9, 2022 at 12:37