Can this be done....?

[Dan B]

I have 2 sheets with customer contact information. The first list may have
several contacts under the same company, with their contact information,
including email address. The second list (on a different tab) is just
company information, no people, no email addresses. There are companies on
the first list that are not on the second. I want to copy any one email
address, with first and last name to the second list when the company name
on the second list matches one on the first.

For example:
Sheet1
A B C....
L
Company First Name Last Name
email


Sheet2
A B, C,D,... G H
I
Company First Name Last Name
email

Columns G,H,I on Sheet2 are blank.....(waiting for the right formula)

What I need, to put the formula verbally, is: If sheet2 A:1= Sheet1 A:1, and
sheet1 L:1 is not blank, then copy sheet1 B:1,C:1 and L:1 to sheet2 G:1,H:1
and I:1, respectively. BUT, I only want the first match it finds, not all of
them (if possible). I hope that makes sense

Thanks for any help on this,

Dan

 

[Max]

One way which may suffice ..

Assuming the source data in Sheet1 is within row2 - row100

In Sheet2,

Put in G2, array-enter (i.e. press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$L$2:$L$100<>""),0)),"",IN
DEX(Sheet1!B$2:B$100,MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$L$2:$L$100<>"
"),0)))
Copy G2 to I2

In I2, edit the index range part,
viz: .. INDEX(Sheet1!D$2$100,

to point to col L instead (the email col in Sheet1),
i.e. change it to: .. INDEX(Sheet1!L$2:L$100
and array-enter the formula in I2 after editing
(remember to array-enter!)

Then re-select G2:I2, and copy down as far as required

Cols G:I will return the desired results

Adapt to suit ..

 

[Dan B]

Thanks for you help. This is partially working. Its just not pulling the
data over on all rows. I noticed that there some differences in the company
names, i.e. some abbreviations etc, so it is not always finding an exact
match. But, I fixed some of those, and it still didn't pull those over.
Any ideas on that?

Thanks

 

[Max]

Thanks for you help. This is partially working. Its just not pulling the
data over on all rows. I noticed that there some differences in the company
names, i.e. some abbreviations etc, so it is not always finding an exact
match. But, I fixed some of those, and it still didn't pull those over.
Any ideas on that?

There could be extraneous white spaces (leading, in-between or trailing
spaces), which are throwing the matching off. We could try wrapping TRIM
around to improve the robustness of the matching.

Try instead in G2, array-entered:
=IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$1
00)<>""),0)),"",INDEX(TRIM(Sheet1!B$2:B$100),MATCH(1,(TRIM(Sheet1!$A$2:$A$10
0)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$100)<>""),0)))

(copy to I2, amend I2, then re-fill the formula as before)

---

 

[Dan B]

That made a big difference. I think the rest is just getting exact matches
in the company names. I'll just have to fix those manually. Thank you for
your expertise. I wish I knew Excel as well as you!

Thanks again,

Dan

 

[Max]

Glad it helped, Dan !
Thanks for the feedback ..