Aligning two tables in Excel on unique keys

Question

I have two tables with the same columns. Each table has a "name" column (unique key basically) and two amount columns.

Each name may appear in either or both table.

What I want to do is align them up so that each instance of the same name appears side by side.

Example:

If the "input" is

Table 1:
Name      Amount1   Amount2
Field A   1         2
Field B   3         4
Field C   5         6

Table 2
Name      Amount1   Amount2
Field B   7         8
Field D   9         10

Then the "output" would be:

Name      Amount1   Amount2       Name      Amount1   Amount2  
Field A   1         2
Field B   3         4             Field B   7         8
Field C   5         6
                                  Field D   9         10

Is there any way of doing this in Excel (short of writing a macro)?

Answer 1

Basically, you make your third (joined) table with a list of all the names.

For the value cells, you need to do a lookup on the value columns of the other tables from that compares the name fields of the two tables.

You need to do a lookup for each value field (so 4 altogether in your example) but it should work.

OK check this spreadsheet for an example.