arraysif-statementgoogle-sheetsarray-formulasgs-vlookup

"Joining" tables in Google Sheets

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?

Solution

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

    1. Use formulas
    2. Use Google Apps Script

    Using formulas

    NOTES:

    Resources

    Using Google Apps Script

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

    Resources

    Related