compare two files and match id

hello please see the attached documents.

I want to compare the colour column in book1 to the colour column in book 2. If I find a match I want to take the id from book1 and port them over to book2. How do I go about doing this?

With both workbooks open:

in Book2, cell B2 and copy down

=INDEX([Book1.xls]Sheet1!$A$2:$B$11,MATCH(A2,[Book1.xls]Sheet1!$B$2:$B$11,0),1)

Book1 can be closed, the formula still works and the formula will then show the complete path name.

hth

with both workbooks open use in book 2 b2 dragged down
=INDEX('[Book1(1).xls]Sheet1'!$A$1:$A$200,MATCH(A2,'[Book1(1).xls]Sheet1'!$B$1:$B$200,0))

See? two people at opposite ends of the world with (almost) identical solutions. At least the approach is the same.

Martin, you must have had a file called Book1.xls in the folder already when you downloaded the attachment, hence the Book1(1).xls name, right?

thanks both of you, both are working!

Now a further 2 questions.

1) When dragged down, those rows that do not have a id assigned appear as #N/A. How can I just have these as blank?

2) The formula obviously connects book1 and book2. So i assume if I delete book1 then book2 will fail.

After I have got the values I want in book2 I dont actually want to keep a link between the files. I just want the data standalone in book2 - how do i go about doing this?

1) When dragged down those rows that do not have a id assign appear as #N/A. How can I just have these as blank.

Let's take Martin's formula and add error checking

=IF(ISNA(MATCH(A2,'[Book1.xls]Sheet1'!$B$1:$B$200,0)),"",INDEX('[Book1.xls]Sheet1'!$A$1:$A$200,MATCH(A2,'[Book1.xls]Sheet1'!$B$1:$B$200,0)))

2) The formula obviously connects book1 and book2. So i assume if I delete book1 then book2 will fail.

Correct.

Once you see all the values in Book2, highlight column B, copy, then paste special and select "Values". This will overwrite the formulas with the results of the formulas. Now you can trash Book1 and Book2 will still be good.

hth

tyvm

???
The name is teylyn.

Ahhhh! Some of these acronyms escape the poor non-native speaker of English.

ty

taylynne ( ) yep i did have some other workbooks open and did the function by selecting the ranges rather than manually entering them