Hi all,

Ive attached a sample to "try" and explain what I am doing. Basically what I would like to achieve is to compare 2 spreadsheets within different workbooks which hold the same text within certain columns but different values in other columns and replace the data in say workbook 1 with whats in workbook 2.

So based on the attached, workbook 1 has simular data in row 2 except for columns B and F (TEST 2 and TEST 6) how can I get the value from columns B and F based on the matching value of column A and place it into Book 1?



Thanks!!

Attached Files

	Book1.xlsx (8.3 KB, 7 views)
	Book2.xlsx (8.3 KB, 7 views)

Assuming all the records in Book1 are found in Book2, you can do this with an array formula. For example, with both workbooks open, the following will update column B:
=INDEX([Book2.xlsx]Sheet1!$B1:$B10,MATCH(A2&C2&D2&E2,[Book2.xlsx]Sheet1!$A1:$A10&[Book2.xlsx]Sheet1!$C1:$C10&[Book2.xlsx]Sheet1!$D1:$D10&[Book2.xlsx]Sheet1!$E1:$E10,0))
For column F, change '$B1:$B10' to '$F1:$F10'. Also, assuming you have more than 10 rows, change all the 10s in the formulae to whatever is the maximum nuumber of rows in Book2.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]