Combining information in sets of columns

K

Ken

I have two spreadsheets which share common elements in one column. The other
columns have different information. I would like to combine them such that
the information in columns are aligned.

As an example: Assume I have associated columns with the values a-z in one
and words beginning in the letters a-z in another and I want to add more
words from two columns on a different spread sheet, let's say one with
letters b, l, n, q, r and asssociated words for these letters in the next
column. I would like the final result to be columns a-z, with the original
associated words and another column with the additionl words in a new column
for b, l, n, q, r. How do I do it?
 
M

Max

Assume you have in Sheet1, cols A and B, from row2 down

a ask
b bad
c cat
etc

(Sheet1's col A is assumed to contain the complete reference list, all are
uniques)

And in Sheet2, you have in cols A and B, from row2 down

b boy
c clone
a akin
etc

(Listing in col A may be incomplete, but assumed to contain no duplicates)

In Sheet1,

Put in C2:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)))
Copy down as far as required to pull over the items from Sheet2's col B
aligned with the complete reference list in Sheet1's col A.

For the sample above, we'd get:

a ask akin
b bad boy
c cat clone
 
Top