7

As an example, say I have the following sheets in the same workbook of a Google Doc:

 SHEET1            |   SHEET2
 \   A  |  B       |   \   A   |   B   |   C   |   D
1|  ID  |Lookup    |  1| Lookup| Name  |Flavor | Color
2|  123 | 4445     |  2| 1234  |Whizzer|Cherry | Red
3|  234 | 4445     |  3| 4445  |Fizzer |Lemon  | Yellow
4|  124 | 1234     |  4| 9887  |Sizzle |Lime   | Blue
5|  767 | 1234     |
6|  555 | 9887     |

Obviously, Google Docs isn't made with relational databases in mind, but I am trying to obtain results similar to the SQL query

SELECT
  SHEET1.ID,
  SHEET2.*
FROM 
  SHEET1 
    LEFT JOIN
      SHEET2
    ON SHEET1.Lookup = SHEET2.Lookup

resulting in a table that looks like

 SHEET3            
 \   A    |   B   |   C   |   D   |   E   
1|   ID   |Lookup | Name  |Flavor | Color
2|  123   | 4445  |Fizzer |Lemon  | Yellow
3|  234   | 4445  |Fizzer |Lemon  | Yellow
4|  124   | 1234  |Whizzer|Cherry | Red
5|  767   | 1234  |Whizzer|Cherry | Red
6|  555   | 9887  |Sizzle |Lime   | Blue

but this is where I stand currently

 SHEET3            
 \   A    |   B   |   C   |   D   |   E   
1|        |       |       |       |
2|  123   | 4445  | #N/A  |       |
3|  234   | 4445  |       |       |
4|  124   | 1234  |       |       |
5|  767   | 1234  |       |       |
6|  555   | 9887  |       |       |

At the moment I have managed to use the QUERY function to grab the values from SHEET1 and have tried a few different QUERY functions in SHEET3!C1 in an attempt to "LEFT JOIN" the two sheets using this blog post as a reference. At this point, the two functions I am using are as follows.

SHEET3!A2=QUERY(SHEET1!A2:B20, "SELECT A,B")
SHEET3!C2=QUERY(SHEET2!A2:E20, "SELECT B,C,D WHERE A="""&B2&"""")

and hovering over the error in C2 reads "Query completed with an empty output". How can I join these sheets?

Additional references:
Google Docs syntax page for QUERY

Fritz
  • 624
  • 1
  • 7
  • 14
  • Added desired results. I assumed that the SQL code I was trying to emulate was enough, but it's not possible to be too thorough. – Fritz Sep 03 '15 at 12:16

4 Answers4

5

Do this in Sheet3.

In cell A1, to get the correct headings:

={Sheet1!A1:B1,Sheet2!B1:D1}

In cell A2, to get the table of Joined data, try this formula:

=FILTER({Sheet1!A2:B,
 VLOOKUP(Sheet1!B2:B, {Sheet2!A2:A, Sheet2!B2:D}, {2,3,4}, false)},
 Sheet1!B2:B<>"")

I've written a comprehensive guide about this topic called:

'Mastering Join-formulas in Google Sheets'

Community
  • 1
  • 1
Viktor
  • 279
  • 3
  • 5
1

If you copy SHEET1 into SHEET3 (A1) then in C2: 

=vlookup($B2,Sheet2!$A:$D,column()-1,0)

copied across and down should give the results you show once you have added three column labels.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Mate, you can get a table of joined data with only one formula (in one cell), given that you combine Vlookup with Arrayformula or the Filter-function and curly brackets. No need to copy across and down. – Viktor Aug 26 '19 at 14:30
0

The following Add-on will provide all you need: Formulas by Top Contributors, by using both the build-in SQL join types and the MATRIX formula:

=MATRIX(SQLINNERJOIN(Sheet1!A1:B6,2,Sheet2!A1:D4,1, TRUE),,"3")

It will yield the following outcome:
enter image description here

I've created an example file for you: SQL JOINS

Affiliation: as a Google Top Contributor I helped creating the Add-on

Jacob Jan Tuinstra
  • 1,197
  • 3
  • 19
  • 50
0

Sheet3!A2: 

=ARRAYFORMULA(Sheet1!A2:B20)

Sheet3!C2: 

=ARRAYFORMULA(VLOOKUP(B2:B20,A1:D50,{2,3,4},0))
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Try encapsulating the Vlookup-function with another pair of curly brackets and it might work better... – Viktor Aug 26 '19 at 14:40