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" <demechanik@yahoo.com> wrote in message
news:ed8UP5RLGHA.964@tk2msftngp13.phx.gbl...
> 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" <none@none.com> wrote in message
> news:O67mkSQLGHA.1124@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>>
>
>