Mastering Join-formulas in Google Sheets

	A	B	C	D	H
1	Mastering Join-formulas in Google Sheets
2
3	In these tabs, you'll learn how to Join data together from different tables of data in Google Sheets, quite similar in efficiency to how a Join clause is used in SQL. Table of contents for this guide:
4
5	Tab Nr.	Name
6	1	Introduction to Join-formulas
7	2	Join-formulas and theory
8	3	2-Columns Join-formulas *
9	4	Raw1 *
10	5	Raw2 *
11	6	Raw3 *
12	7	Explanation of 2-Columns Join-formulas
13	8	Bonus section 1: the Query function
14	9	Bonus section 2: Advanced Data Blending
15
16	* These four Tabs has been created as a complex example for how to create 2-columns Join-formulas. The specific formula is to be found in Tab Nr. 3, while the Tabs Nr. 4, 5 and 6 contains the raw-data (from which the formula get the data). In Tab Nr. 7, you'll find in depth explanations for how the Join-formula works in Tab Nr. 3
17
18
19	___________________________________________________________________________________________________________________________________________________________________________
20
21
22	Within this tab-document:
23
24	Join-formulas, what they are and how to use them				Row: 43
25
26	Functions which has to be understood to create Join-formulas				Row: 56
27
28		-Arrayformula			Row: 58
29
30		-Curly Brackets			Row: 87
31
32		-Vlookup			Row: 117
33
34		-Sources to learn more about these functions			Row: 185
35
36
37
38
39
40
41
42
43	Join-formulas, what they are and how to use them
44
45	With the use of Join-formulas it's possible to dynamically Join different tables of data together. Let's say that you're working with data from Google Analytics and you would like to Join data together from many different reports, or let's say that you would like to combine data from many different contexts such as: SQL-databases, Google Analytics, Facebook Ads Manager, Snapchat Ads Manager, Google Adwords etc. (basically any data which can be imported to Google Sheets with a tool such as Super Metrics).
46	When using Join-formulas, you have to use at least one column as a common denominator which can be found in all of the different tables you're working with, to be able to accurately get the data to be joined from the different tables (it's important that all of the values in this particular column have unique values). The beauty with Join-formulas is that all of this can be achieved with writing only one specific formula in one cell.
47	The functions which are necessary for creating Join-formulas are: Arrayformula, Curly Brackets and Vlookup. While it is possible to create Join-formulas using the Query-function, this won't be discussed in this guide since it's a lot less reliable anyway (compared to the Vlookup-function). The Query-function can however be used in conjunction with Join-formulas to further define how the data is to be returned by the formula; which for example can be quite useful for evaluating interesting KPIs. This will be conveyed later on in the guide.
48
49
50
51
52
53
54
55
56	Functions which has to be understood to create Join-formulas
57
58	Arrayformula
59
60	With the use of Arrayformula it's possible to return a range of cells, compared to ordinary formulas which only returns a single value.
61	Hence, the benefit of Arraformulas is that you can get columns of data from just one defined formula in one cell!
62
63	In this example we can see from the defined formula in the cell A75, that it's possible to return all of the raw data from just one cell:
64
65
66
67	Raw data:
68	A	B	C
69	A	B	C
70	A	B	C
71	A	B	C
72
73
74	Formula:
75	A	B	C
76	A	B	C
77	A	B	C
78	A	B	C
79
80
81	This however, is a rather static way of returning data with the use of Arrayformula. If we want a lot more flexibility using Arrayformula, where it's possible to restructure the order in how the columns of data is returned, we need to use Curly Brackets within the Arrayformula.
82
83
84
85
86
87	Curly Brackets { }
88
89	Curly Brackets are useful whenever we want to write a more complex formula using Arrayformula, and we want to make cell references in a more flexible manner, restructure the order of data and also including other functions within an Arrayformula such as the Vlookup-function.
90	Using Curly Brackets means a lot more flexibility and without Curly Brackets, Arrayformula tends to be more static in its functionality.
91
92
93
94	3 examples of how Arrayformulas can be combined with Curly Brackets and used for returning multiple columns of data.
95	Look for cells A106, E106 & I106 for formula references.
96
97
98	Raw data:
99	A	B	C	1
100	A	B	C	2