I have been struggling with a formula - help!
I want to copy data from one sheet to another but only if there is an exact
match of data.

I need help with the structure of the formula - I can massage it later to
fit the specifics.

First, I need to find any exact match of the text in D1 of Doc1 to any row
in column E of Doc2.

If there is a match, then:
from that match's row in Doc2, I want to enter the data from column A into
column B of L1's row in Doc1.
If there is no match, nothing happens.

Can anyone help? Spent hours trying to set this up. I need the formula -- or
if someone knows how to write a macro to do this, that would work too.
Doc1
A B C D E F
1 AB
2 CD
3 ED
4 GH
5 IJ
6 KL

Doc2
A B C D E
1 4 NO
2 6 ST
3 -4 ED
4 5 MO
5 5 IJ
6 -5 AB
7 2 CD
8 5 KL


Doc1
A B C D
1 -5 AB
2 2 CD
3 -4 ED
4 GH
5 5 IJ
6 5 KL

How about this in column B of Doc1?

=IF(ISNA(INDEX(Doc2!A$1:A$8,MATCH(D1,Doc2!E$1:E$8,0),1)),"",INDEX(Doc2!A$1:A$8,MATCH(D1,Doc2!E$1:E$8,0),1))

Naturally, change the $8 values to the last row used on Doc2 sheet. The
IF(ISNA()) portion suppresses the #NA that would appear for GH on Doc1. The
catch here is that MATCH() is not case sensitive, so AB=ab=AB=Ab=Ab.

You could also use LOOKUP() but the entries on Doc2 sheet, column E would
have to be in ascending order to work properly.


"cap1816" wrote:

> I have been struggling with a formula - help!
> I want to copy data from one sheet to another but only if there is an exact
> match of data.
>
> I need help with the structure of the formula - I can massage it later to
> fit the specifics.
>
> First, I need to find any exact match of the text in D1 of Doc1 to any row
> in column E of Doc2.
>
> If there is a match, then:
> from that match's row in Doc2, I want to enter the data from column A into
> column B of L1's row in Doc1.
> If there is no match, nothing happens.
>
> Can anyone help? Spent hours trying to set this up. I need the formula -- or
> if someone knows how to write a macro to do this, that would work too.
> Doc1
> A B C D E F
> 1 AB
> 2 CD
> 3 ED
> 4 GH
> 5 IJ
> 6 KL
>
> Doc2
> A B C D E
> 1 4 NO
> 2 6 ST
> 3 -4 ED
> 4 5 MO
> 5 5 IJ
> 6 -5 AB
> 7 2 CD
> 8 5 KL
>
>
> Doc1
> A B C D
> 1 -5 AB
> 2 2 CD
> 3 -4 ED
> 4 GH
> 5 5 IJ
> 6 5 KL
>
>