Concatenate function with if statement for creating mailing list salutations

[LadyMagnus]

Hi, I work for an organization that has just changed our CRM. Our previous CRM database allowed for us to save salutations for the purposes of creating mailing lists. The new database doesn't have that feature for exported mailing lists, so I am trying to recreate it with the Concatenate function.

The end goal of our salutations is that if there are two people on the record the salutation will be as follows:

John and Jane Doe (if their last name is the same) or John Smith and Jane Doe (if their last name is different)

I wrote this formula, but and I'm very close to get the correct output, but the results are slightly off:
=CONCATENATE(A2, IF(B2=D2, " and ", B2), C2, IF(B2=D2, B2, D2))

below is my sample data and results.

[TABLE="width: 700"]
<tbody>[TR]
[TD]First_firstname[/TD]
[TD]First_lastname[/TD]
[TD]Second_firstname[/TD]
[TD]Second_lastname[/TD]
[TD]Concatenated[/TD]
[TD]DesiredResults[/TD]
[/TR]
[TR]
[TD]Johnny[/TD]
[TD]Giftcard[/TD]
[TD]Sally[/TD]
[TD]Patron[/TD]
[TD]JohnnyGiftcardSallyPatron[/TD]
[TD]Johnny Giftcard and Sally Patron[/TD]
[/TR]
[TR]
[TD]Ally[/TD]
[TD]Friend[/TD]
[TD]Mike[/TD]
[TD]Friend[/TD]
[TD]Ally and MikeFriend[/TD]
[TD]Ally and Mike Friend[/TD]
[/TR]
</tbody>[/TABLE]


for row 1 I am not getting spaces between the the names and I'm not getting and. For row 1 I am not getting a space between the second_firstname and second_lastname.

any insight would be very appreciated!! Thanks!! I am using Excel 2013

 

[elmer007]

try: =IF(B2=D2,A2&" and "&C2&" "&D2,A2&" "&B2&" and "&C2&" "&D2)

 

[LadyMagnus]

this is working perfectly!!!

Thank you so much! this is much easier than the concatenate function.

note: I used your logic here to fix my concatenate function and came up with: =CONCATENATE(A2,IF(B2=D2," and ", (" " & B2 & " and ")),C2,IF(B2=D2," " & B2, " " & D2)) This gives the same results as the function you provided me, but I like yours better because it doesn't rely on concatenate which is sometimes fussy or problematic.