When preparing mailing labels, customer lists, or household records, you often need to display names differently depending on whether two people share the same last name. Manually formatting each record can be time-consuming, especially for large datasets.

In this tutorial, you’ll learn how to build an Excel formula that automatically combines names based on three common scenarios: two people sharing the same last name, two people with different last names, or a single person. This walkthrough uses IF, AND, and TEXTJOIN functions to create a flexible solution that can easily be adapted for your own spreadsheets.

What You’ll Learn

In this tutorial, you’ll learn how to:

  • Combine names automatically using a single Excel formula.
  • Determine whether two people share the same last name.
  • Display names differently based on three common scenarios.
  • Use IF, AND, and TEXTJOIN together in a practical formula.
  • Create cleaner mailing lists, customer records, and address labels.

How the Formula Works

The formula follows three simple rules:

Scenario 1

If both people exist and share the same last name, combine the first names and display the shared last name.

Example: Brian Smith and Becky Smith = Brian and Becky Smith

Scenario 2

If both people exist but have different last names, display both full names.

Example: Joe Miller and Shayna Simpson = Joe Miller and Shayna Simpson

Scenario 3

If only one person exists, simply display that person’s full name.

Example: Clarice Thompson (and no one else) = Clarice Thompson

Common Uses

This formula is useful whenever names need to be displayed differently depending on household composition.

Common examples include:

  • Mailing labels
  • Holiday card lists
  • Customer databases
  • Household records
  • Donor lists
  • Membership directories
  • Event registrations
  • CRM exports

Requirements & Compatibility

Microsoft 365 / Excel 2021+

Uses: IF, AND, TEXTJOIN

Older versions of Excel

TEXTJOIN isn’t available. An alternate version using the & operator is included in the companion workbook.

Practice & Resources

Practice the formulas from this tutorial using the interactive workbook. It includes sample data, detailed formula explanations, and two versions of the solution:

  • Microsoft 365 / Excel 2021+ using TEXTJOIN
  • Older versions of Excel using the & operator

The workbook is shared as a view-only Google Sheet. You can create your own copy online or download it as an Excel workbook to experiment with the formulas using your own data.

View Workbook

Video Tutorial

Breaking Down the Formula

The formula used in this tutorial:

Formula

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

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))

IF

Determines which formatting rule should be applied. If both people exist and share the same last name, one result is returned. Otherwise, the alternate formatting is used.

AND

Checks three conditions:

  • Person 2 has a first name.
  • Person 2 has a last name.
  • Both last names match.

TEXTJOIN

Combines text while automatically ignoring blank values. This makes it ideal for situations where the second person may not exist.

Result

  • 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.

Step-by-Step Instructions

Step 1: Set up the Excel sheet

Arrange your spreadsheet so Person 1 and Person 2 first and last names occupy separate columns.

Example:

  • Column A: First Name 1
  • Column B: Last Name 1
  • Column C: First Name 2
  • Column D: Last Name 2

Step 2: Enter the formula & update if necessary

Copy the appropriate version of the formula (TEXTJOIN or legacy version) into the output column.

For Excel versions with TEXTJOIN:

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

For older versions of Excel without the TEXTJOIN function:

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

Note: Update the cell references if your worksheet uses a different layout.

Step 3: Copy the formula down the column

Copy the formula down for all remaining records.

Step 4: Check the results

Verify the results using examples where:

  • Only one person exists.
  • Both people share a last name.
  • Both people have different last names.

Additional Tips

Helpful Tip

Convert your data range into an Excel Table before applying the formula. Tables automatically expand as new records are added and make formulas easier to maintain.

Version Differences

The primary formula uses TEXTJOIN, which is available in Microsoft 365 and Excel 2021+.

If you’re using Excel 2019 or earlier, use the alternate formula included in the companion workbook.

Enjoyed this tutorial? Subscribe to my YouTube channel for more step-by-step software tutorials, tips, and practical ways to work more efficiently.