Search code examples
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

    • 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

    Using Google Apps Script

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

    Resources

    Related