I have two tables, each with one common index column. One table has multiple entries of the index value with other information in adjacent columns; the other table has one entry of the index value with information in other columns of the same row.

How can I lookup the index in the first table - locate the matching index in the second table and append the information from the second table to the first table?

  • Is using Access instead of Excel an option? If I'm not mistaken, that would make it far simpler to get the result you want. – Brian Z Sep 6 '13 at 17:50

Since you didn't provide the specifics, I'll make some assumptions.

Table1 has the indexes appearing multiple times. Those index are in column A. The sheet in which Table1 is in is called Sheet1. Column B is where you need data appended from Table2.

Table2 has the indexes appearing only once and they are in column A as well, but the sheet is named Sheet2. The data to be brought into Table1 is in column B.

Both tables have headers, meaning that the actual data starts on row 2.

What you need here is VLOOKUP:

=VLOOKUP(A2, 'Sheet2'!A:B, 2, FALSE)

VLOOKUP takes the index in A2 and looks it into column A of Sheet2. When a match is found, it will return the contents of the corresponding index from column B, the second column in the selected table array (the table array is A:B). FALSE indicates an exact match (TRUE indicates approximate match).

Then you simply have to fill this formula down till the end of Table1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.