Excel - IF conditional to copy column from one spreadsheet to another

Asked By TagTech on 04-Mar-10 04:17 PM
What is the formula to copy email addrs from one sheet column to another
sheet column IF Lname = Lname AND Fname = Fname in the same workbook?

=IF((E2=Sheet1!C2 AND F2=Sheet1!B2),(K2=Sheet1!A2),(K2))

is not getting the job done.  Result = #NAME?




David Biddulph replied to TagTech on 04-Mar-10 04:39 PM
If you look up AND in Excel help, it will show you the syntax for the AND
function.
--
David Biddulph
Fred Smith replied to TagTech on 04-Mar-10 05:22 PM
Try it this way:
=if(and(e2=sheet1!c2,f2=sheet1!b2),sheet1!a2,k2)

it is unclear for your example what result you want if true, so the above is
my guess at it.

Regards,
Fred
TagTech replied to Fred Smith on 05-Mar-10 04:01 PM
OK I must have misspoken.  'IF' only returns '0' / 'Yes' or '1' / 'No' ...
that is not what I want at all - what I want may not even be a "Function".

I have two spreadsheets / workbooks whatever you call them ... two separate
'.xls' entities.

In the spreadsheet I want to modify, I have many columns, three of which
columns are 'FirstName', 'LastName' and 'Email'.  'FirstName' and 'LastName'
have people's names in them; the column 'Email' has no entries in it at all -
this is the column I want to populate.

In the other spreadsheet, I also have the same three columns but the 'Email'
column does have addresses populating it.

I want to compare 'LastName's and 'FirstName's between the two sheets so
that I get a perfect match.  When I do get a perfect match, I want to
populate the empty 'Email' column in the spreadsheet I want to modify with
the correct email address for that unique individual.

In other words, I may have many "Johnson"s in both sheets; "Dale", "Jimmy",
one spreadsheet to populate the other spreadsheet.

-Thanks for the replies.
Fred Smith replied to TagTech on 05-Mar-10 07:21 PM
Here is what I would do.
Add a column to each sheet which is =Lastname & Firstname. Now you have a
unique name. Let's say this is in column J, and the e-mail address you want
to pick up is in column K. Your Lastname&Firstname combination is in column
A of your other sheet.

To get the address, use:
=vlookup(a1,sheet1!J:K,2,false)

Regards,
Fred