How do I join two tables with LibreOffice Calc?

Question

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?

Answer 1

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).