CODEWITHSUNDEEP
exceljoin
blunders
blunders

Reputation: 3669

What is the best way to join/merge two tables by column cell matching in Excel?

I've found this excel add-in to buy that appears to do what I need, but I'd rather have code that's open to use as I wish. While a GUI is nice, it's not required.

In an attempt to make the question more clear, I'm adding some two sample "input" tables in tab delimited form, and the resulting output table:

SAMPLE_INPUT_TABLE_01

horse<tab>age<tab>body
a<tab>1<tab>A
b<tab>2<tab>B
b<tab>3<tab>C
c<tab>4<tab>D
e<tab>5<tab>E
f<tab>6<tab>F

SAMPLE_INPUT_TABLE_02

rider<tab>horse<tab>finish<tab>date
xxx<tab>a<tab>yy<tab>zz
xxx<tab>b<tab>yy<tab>zz
xxx<tab>b<tab>yy<tab>zz
xxx<tab>c<tab>yy<tab>zz
xxx<tab>e<tab>yy<tab>zz
xxx<tab>f<tab>yy<tab>zz

SAMPLE_OUTPUT_TABLE_03

rider<tab>horse<tab>finish<tab>date<tab>age<tab>body
xxx<tab>a<tab>yy<tab>zz<tab>1<tab>A
xxx<tab>b<tab>yy<tab>zz<tab>3<tab>C
xxx<tab>b<tab>yy<tab>zz<tab>3<tab>C
xxx<tab>c<tab>yy<tab>zz<tab>4<tab>D
xxx<tab>e<tab>yy<tab>zz<tab>5<tab>E
xxx<tab>f<tab>yy<tab>zz<tab>6<tab>F

If it matters, I'm using Office 2010 on Windows 7.

Upvotes: 3

Views: 8352

Answers (2)

Dr. belisarius
Dr. belisarius

Reputation: 61026

I think this emulates the example in your link:

alt text

The formulas are:

In I2 (and copied down): 

=VLOOKUP(F2,$A$2:$C$7,2)

In J2 (and copied down: 

=VLOOKUP(F2,$A$2:$C$7,3)

Upvotes: 7

Yuval F
Yuval F

Reputation: 20621

I needed this yesterday, and vlookup solved my problem. Basically, you look for a join value from one of the tables inside the other one, and then use a column offset to get another column from the same row. This may become tedious when your tables have many columns, and does not support left join, but may be enough for your use case.

Upvotes: 1

Related Questions