Function to intelligently concatenate/combine tables in Excel

Question

Which function in Excel will intelligently combine tables using a common column as an index?

For example: http://s10.postimage.org/8dwh1agfd/superuser.png

Answer 1

1) Make a list of all the unique extensions (the "index" column)

• Paste the extensions from one table below those of the other table so you have one long column with all the extensions (containing duplicates)
• Remove the duplicates, e.g. using http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx

2) Add values for the "name" column

• Create a new column next to the extensions, with the formula =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), where lookup_value is the extension in the new table to look up in the "names" table, table_array is the "names" table, and col_index_num is the column in the "names" table containing the names (column 2).

3) Do the same thing for the "scores" column