CODEWITHSUNDEEP
google-sheetspivot-tablegoogle-docs
Hector B
Hector B

Reputation: 63

Using more than one sheet for a pivot table in Google sheets

I'm trying to link three spreadsheets in Google Sheets by using the pivot tables functionality.

The problem that i have now is that i don't find a way to pull the data for more than one sheet. I can only operate the Pivot table with the information coming from only one. I have researched quite a lot, but my impression so far is that the documentation available for Google Docs is not so extensive at some point.

Basically what i need to do is the following:

Table 1(main):

Car Name | ModelId | ColorID
ford | 1 | 1
fiat | 2 | 2

Table 2:
ModelID | Name
1 | mustang
2 | bravo

Table 3:
ColorID | Name
1 | Red
2 | Blue

Resulting pivot table:
Car Name | Model| Color
ford | mustang | Red
fiat | bravo | Blue

In SQL statements i'm basically trying to simulate a JOIN. I also could write a javascript script but i would like to know if there is a simple way to achieve this without coding.

Thanks!

Upvotes: 5

Views: 12195

Answers (1)

Chris Hick
Chris Hick

Reputation: 3094

This formula reproduces your example output and will update if more records are added to the 3 tables:

={"CarName","Model","Color"; Table1!A2:A, ARRAYFORMULA(IFERROR(VLOOKUP(Table1!B2:B,Table2!A:B,2,0))), ARRAYFORMULA(IFERROR(VLOOKUP(Table1!C2:C,Table3!A:B,2,0)))}

This example sheet shows the formula working.

Upvotes: 7

Related Questions