0

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.

0

  1. if your second table looks like this:

    1

  2. and you want something like this:

    5

  3. then paste this into D2 cell:

    =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), ))

    5

  4. and paste this into F2 cell:

    =ARRAYFORMULA(IF(LEN(A2:A), C2:C*E2:E, ))

    6



  • 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; ))

0
  • 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), ))}}

enter image description here

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.