I have two tables, each in it's own sheet in the same file:
Name Age
Alice 18
Bob 19
Carol 20
Dave 19
Name Gender
Bob Male
Dave Male
Eve Female
I want to automatically create a third table (sheet) that contains the joined data on the column name:
Name Age Gender
Bob 19 Male
Dave 19 Male
Is this operation supported in LibreOffice Calc?
Here's a straightforward way to do it.
Sheet 1:
Sheet 2:
Sheet 3:
On Sheet 3, Name and Age are just cell references to Table 1. So A2 contains:
=Sheet1!A2
And B2 contains:
=Sheet1!B2
Gender is populated by a lookup of that row's name in Table 2. So C2 contains:
=VLOOKUP(Sheet3!A2,Sheet2!A:B,2,0)
Note that Tables 1 and 2 don't need to be in the same order.
BTW, these formulas reflect setting preferences to emulate Excel formula syntax in Tools | Options | LibreOffice Calc | Formula.
Revision for comment
Here's a trivial way to adapt this approach for cases where Tables 1 and 2 don't match and you want only the common records. Say Table 2 looks like your example:
The existing formula would produce this:
Alice and Carol don't find a match in Table 2. Eve wasn't in Table 1, so there won't be a match for her, either. Turning on filtering lets you hide the #N/A records:
If you want to have a clean table where those records are actually gone, Copy and Paste Special values (in LO Calc, I selected Text and Numbers but not formulas), will paste only the non-hidden rows. That's shown in rows 10-12 (notice no hidden rows).
