Join tables on Google Sheet

Question

How can I join 2 tables on Google Sheet using a column as a key reference?

I have my data on Sheet1 and Sheet2.

What formula should I insert on Sheet3!A1 to get this result?

---

Sheet1

|   | A          | B          |
| - | ---------- | ---------- |
| 1 | Date       | Task 1 (h) |
| 2 | 2021-01-01 | 1          |
| 3 | 2021-01-02 |            |
| 4 | 2021-01-04 | 1          |
| 5 | 2021-01-06 | 1          |

---

Sheet2

|   | A          | B          | C          |
| - | ---------- | ---------- | ---------- |
| 1 | Date       | Task 2 (h) | Task 3 (h) |
| 2 | 2021-01-01 | 2          |            |
| 3 | 2021-01-03 |            | 3          |
| 4 | 2021-01-05 |            | 3          |
| 5 | 2021-01-06 |            |            |

---

Sheet3

|   | A          | B          | C          | D          |
| - | ---------- | ---------- | ---------- | ---------- |
| 1 | Date       | Task 1 (h) | Task 2 (h) | Task 3 (h) |
| 2 | 2021-01-01 | 1          | 2          |            |
| 3 | 2021-01-02 |            |            |            |
| 4 | 2021-01-03 |            |            | 3          |
| 5 | 2021-01-04 | 1          |            |            |
| 6 | 2021-01-05 |            |            | 3          |
| 7 | 2021-01-06 | 1          |            |            |

Answer 1

The proposed answer above recommending the use an ARRAYFORMULA is far more complicated than it needs to be for your simple problem.

Search for tutorials on the INDEX-MATCH function. This will allow you to perform the table join once you have the full key column.

To get the full key column, use the UNIQUE function to feed in column A from both sheets to get a unique list of keys.