Merge matched data from two spreadsheets

[docrogue]

Hi all,

I searched the forum, but could not find a solution for my problem. Any help is appreciated. I have one spreadsheet with Data A and Data B, a second spreadsheet with Data A and Data C. I need to copy Data C from the second spreadsheet into the first spreadsheet for each line that matches Data A. Example below:


Spreadsheet 1 Spreadsheet 2

Column A Column B Column A Column C

100 X 50 2

104 X 100 6

106 X 106 8


I need the new spreadsheet 1 to look like:

Column A Column B Column C

100 X 6

104 X

106 X 8

 

[Hui]

Docrogue,

Assuming the 2 tables start in A1

with Spreadhseet 1 in Sheet 1 and Spreadsheet 2 in Sheet 2

The following will do it for you:

Sheet 1 C1: =INDEX(Sheet2!$A$1:$B$3,MATCH(A1,Sheet2!$A$1:$A$3,0),2)

Adjust $B$3 to suit

and copy down


If they are in different files add file names as appropriate

 

[docrogue]

Hui,

Thanks for the reply; I tried the formula and received a #N/A. I think there should be an IF command to match if Column A in Sheet 1 matches Column A in Sheet 2 to then bring the value for Column C in that Row on Sheet 2 into the newly "merged" Column C into Sheet 1. I am having a hard time following the INDEX,MATCH function. Sorry for being dense.

 

[kchiba]

Hi Docrouge,,

Have you tried vlookup() with ISNA()?


=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,0)),"",VLOOKUP(A1,Sheet2!$A$1:$B$2,2,0))


cheers


kanti

 

[Hui]

Docrogue

Have a look at a mockup at

http://rapidshare.com/files/401231168/Docrogue.xls.html

 

[docrogue]

Thanks for the help Hui - it worked great. On one of the spreadsheets the numbers were formatted as text - so no matches were found. I converted them and all works great! Thanks, there were 15000 lines of data!