# LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements):

``````product_id | material | required
1          | A        | 0.5
1          | B        | 0.7
2          | A        | 0.2
3          | A        | 0.12
``````

And Table B (orders):

``````order_id | product_id | quantity
1        | 1          | 100
2        | 2          | 10
``````

I want to do a `LEFT JOIN`* on these two tables (table B being outer), such that the result would be:

``````order_id | product_id | quantity | material | required
1        | 1          | 100      | A        | 0.5
1        | 1          | 100      | B        | 0.7
2        | 2          | 10       | A        | 0.2
``````

How do I do this? Of course, the end goal is to multiply the `required` with the `quantity`

Ideally, there should be an additional table with just basic product information, e.g. something like:

``````product_id | name | description
``````

*It should be `LEFT JOIN`, NOT `INNER JOIN` simply because all `orders` should be shown, even if the info in product material description is not yet input. An order cannot be 'forgotten' because inner join is used.

1. if your second table looks like this: 2. and you want something like this: 3. then paste this into D2 cell:

``=ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), ))`` 4. and paste this into F2 cell:

``=ARRAYFORMULA(IF(LEN(A2:A), C2:C*E2:E, ))`` • if you are not american use:
``=ARRAYFORMULA(IFERROR(VLOOKUP(B2:B; Sheet2!A2:C; {2\3}; 0); ))``
``=ARRAYFORMULA(IF(LEN(A2:A); C2:C*E2:E; ))``

• one-cell solution (paste in D1):

``````={"material", "required", "total";
ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), )),
ARRAYFORMULA(IF(LEN(A2:A), C2:C*VLOOKUP(B2:B, Sheet2!A2:C, 3, 0), ))}`````` • if you want it to export into Sheet3 use:

``````={{QUERY(Sheet1!A1:C, "select *", 1)},
{"material", "required", "total";
ARRAYFORMULA(IFERROR(VLOOKUP(Sheet1!B2:B, Sheet2!A2:C, {2,3}, 0), )),
ARRAYFORMULA(IF(LEN(Sheet1!A2:A), Sheet1!C2:C*VLOOKUP(Sheet1!B2:B, Sheet2!A2:C, 3, 0), ))}}`````` Let's say your Table A were in A1:C, and your Table B were in E1:G. The following array formula would generate the table you indicate (placed anywhere other than Columns A:G):

`=ArrayFormula(QUERY({FILTER(A2:C,ISNUMBER(FIND("\"&A2:A&"\","\"&TEXTJOIN("\",TRUE,E\$2:E)&"\"))),VLOOKUP(FILTER(A2:A,ISNUMBER(FIND("\"&A2:A&"\","\"&TEXTJOIN("\",TRUE,E\$2:\$E)&"\"))),QUERY({E\$2:G},"Select Col2, Col1, Col3 Where Col1 is Not Null"),{2,3},FALSE)}, "Select Col4, Col1, Col5, Col2, Col3, Col5*Col3 Where Col1 Is Not Null Label Col4 'order_id', Col1 'product_id', Col5 'quantity', Col2 'material', Col3 'required', Col5*Col3 'tot_material'"))`

If it works as you expect, and you want to understand it, check back in here. But I do want to make sure it is what you want before diving into explaining something this complex. (If it works and you're happy to just use it without explanation, also fine.)