In this video, I demonstrate how to use an Excel formula to join first and last names for two people based on whether or not they share the same last name. This is an efficient way to prepare names for printing address labels when there may be different name scenarios.

The Rules:

  • If person 1 and person 2 exist and share the same last name, join the first names and use the common last name
  • If person 1 and person 2 exist but do NOT share the same last name, join their full names
  • If person 1 exists but person 2 does not exist, join the person’s first and last names
In this example, we have the first and last names for person 1 and person 2 (where applicable) in columns A-D and the formula result in column E. The formula will review the cells to determine whether there are two people and if so, whether they share the same last name or not, then join the names accordingly.

The Formula:

=IF(AND(C2<>””, D2<>””, B2=D2), TEXTJOIN(” and “, TRUE, A2, C2) & ” ” & B2, TEXTJOIN(” and “, TRUE, TEXTJOIN(” “, TRUE, A2, B2), TEXTJOIN(” “, TRUE, C2, D2)))

The formula is based on using the same layout as the screenshot above. If your spreadsheet is laid out differently, it will be necessary to adjust the formula to match your layout. View working doc in Google Sheets

NOTE: If you are using an older version of Excel that does not offer the TEXTJOIN function, the “&” function can be used instead:

=IF(AND(C2<>””, D2<>””, B2=D2), A2 & ” and ” & C2 & ” ” & B2, IF(AND(C2<>””, D2<>””), A2 & ” ” & B2 & ” and ” & C2 & ” ” & D2, A2 & ” ” & B2))

Step-by-Step Process:

Formula Breakdown
The formula: =IF(AND(C2<>””, D2<>””, B2=D2), TEXTJOIN(” and “, TRUE, A2, C2) & ” ” & B2, TEXTJOIN(” and “, TRUE, TEXTJOIN(” “, TRUE, A2, B2), TEXTJOIN(” “, TRUE, C2, D2)))
IF(condition, value_if_true, value_if_false)
· Checks whether names for both people exist and if so, whether the last names match
· If true, the first names will be joined with “and” and use the shared last name
· If false, the names will be combined separately
AND(C2<>””, D2<>””, B2=D2)
· C2<>”” → Ensures Person 2 has a first name
· D2<>”” → Ensures Person 2 has a last name
· B2=D2 → Checks if both last names are the same
TEXTJOIN(” and “, TRUE, A2, C2) & ” ” & B2
· For when the last names match, value if true
· TEXTJOIN(” and “, TRUE, A2, C2) → Joins the first names (e.g., “Brian and Becky”)
· & ” ” & B2 → Appends the shared last name (e.g., “Smith”)
TEXTJOIN(” and “, TRUE, TEXTJOIN(” “, TRUE, A2, B2), TEXTJOIN(” “, TRUE, C2, D2))
· For when last names DON’T match, value if false
· This applies when a second person exists but last names do not match OR if only 1 person exists
· TEXTJOIN(” “, TRUE, A2, B2) → Joins the first and last name of Person 1 (e.g., “Clarice Thompson”)
· TEXTJOIN(” “, TRUE, C2, D2) → Joins the first and last name of Person 2 (if they exist, e.g., “Shayna Simpson”)
· TEXTJOIN(” and “, TRUE, [Person 1 full name], [Person 2 full name])
· If Person 2 exists, it joins both full names (e.g., “Joe Miller and Shayna Simpson”)
· If Person 2 does not exist, it only returns “Clarice Thompson”

Need Microsoft Office? Get a subscription here: https://amzn.to/4i5UN5l

As an Amazon Associate I earn from qualifying purchases.

Don’t forget to subscribe to my channel for more tutorials!