"Joining" tables in Google Sheets

Question

I have two tables. The first one looks somewhat like this:

id   name
----------
0    "abc"
1    "def"
2    "ghi"
3    "jkl"
4    "mno"
5    "pqr"

... and the second one looks like this:

id1  id2  foo
-------------
3    1    "a"
1    5    "b"
5    6    "c"
5    2    "d"
4    1    "e"
2    5    "f"

So, now I want a third table that is equivalent to the second one, but instead of the ids (id1 and id2), I want the name corresponding to that id. So, for instance:

name1  name2  foo
-----------------
"jkl"  "def"  "a"
"def"  "pqr"  "b"
...    ...    ...

The idea behind all of this is that I want to print the third table, but I do not want to modify it directly. Instead, I just want to alter tables one and two.

Is this somehow possible in Google Sheets?

score 2 · Answer 1 · answered Aug 23 '20 at 19:17

2

use:

=ARRAYFORMULA({IF(F1:F="",,IFNA(VLOOKUP(D1:E, A1:B, 2, 0))), F1:F}

answered Aug 23 '20 at 19:17

player0

124,011
12
67
124

Rubén · Accepted Answer · 2020-08-23T18:50:55.930

Yes, it's possible, actually there are two common paths to do this

Use formulas
Use Google Apps Script

Using formulas

Use VLOOPKUP

NOTES:

You could use ARRAYFORMULA to do this on a single formula
Instead of VLOOKUP you could combine INDEX and MATCH functions

Resources

https://support.google.com/docs Search for the function list or on the Google Sheets UI click on Help > Functions

Using Google Apps Script

In order to use Google Apps Script you should know the basics of JavaScript.

Resources

https://developers.google.com/apps-script/guides/sheets

"Joining" tables in Google Sheets

2 Answers2