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.

First_firstname	First_lastname	Second_firstname	Second_lastname	Concatenated	DesiredResults
Johnny	Giftcard	Sally	Patron	JohnnyGiftcardSallyPatron	Johnny Giftcard and Sally Patron
Ally	Friend	Mike	Friend	Ally and MikeFriend	Ally and Mike Friend

<tbody>
</tbody>

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.