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

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:D$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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" wrote in message
...
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

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" wrote in message
...
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:D$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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" wrote in message
...
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

"Dan B" wrote:
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($A 2))*(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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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" wrote in message
...
"Dan B" wrote:
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($A 2))*(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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad it helped, Dan !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" wrote in message
...
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