0

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          |            |            |
genna
  • 461
  • 1
  • 6
  • 17
  • 3
    Does this answer your question? [Join tables in google sheet - full join](https://stackoverflow.com/questions/62641416/join-tables-in-google-sheet-full-join) – ZygD Mar 29 '21 at 13:48
  • It probably should, but I thought it would be WAY easier – genna Mar 29 '21 at 16:55

1 Answers1

2

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.

Adam Stevenson
  • 657
  • 3
  • 11
  • With this approach every desired cell in Sheet3 needs a formula in it, right? – genna Mar 30 '21 at 08:22
  • Correct. You can autofill the formula from the first row down the entire sheet. https://support.google.com/docs/answer/75509?co=GENIE.Platform%3DDesktop&hl=en – Adam Stevenson Mar 30 '21 at 23:53