Function Problems

Excel Formula to Identify Contacts with Matching Last Names

Kushani Nimanthika Oct 10, 2026 868 views

Question details

The user needs a spreadsheet formula to compare a column of contact last names against a master reference list, automatically returning 'Yes' if a match is found and 'No' if it is not.

How to Identify Contacts with Matching Last Names Using an Excel Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cross-referencing contact lists to identify which specific last names in one column already exist within a designated reference range.
Observed behavior: Requires a reliable formula output of 'Yes' or 'No', with considerations for potential data inconsistencies like extra spaces or case differences that normally break basic matching functions.

Before you start

Ensure that both your target column and reference list are formatted as text, and briefly check for trailing spaces or hidden characters that could cause false negative results.

Solution 1Recommended

Use IF, ISNUMBER, and MATCH for Exact Matches

This standard combination of formulas checks if a specific target value exists in your reference array and outputs 'Yes' or 'No' based on the result.

The MATCH function looks for a specified item in a range of cells and returns its relative position. When nested inside ISNUMBER, it creates a true/false condition based on whether a valid position was found. The IF function then translates that true/false into 'Yes' or 'No'.

Select the Target Output Cell

Click on cell F2 (or the blank cell adjacent to your first contact name in column E) to make it the active cell.

Input the Match Formula

Type the formula `=IF(ISNUMBER(MATCH(E2,$A$2:$A$640,0)),"Yes","No")` directly into the formula bar. The zero at the end ensures Excel looks for an exact match.

Apply the Formula to All Rows

Press Enter to execute the formula. Next, click the small square (fill handle) at the bottom-right corner of cell F2 and drag it downward to populate the formula for the rest of your contact list.

Use IF, ISNUMBER, and MATCH for Exact Matches

Absolute References: The dollar signs ($) in $A$2:$A$640 create an absolute reference, ensuring that your reference list does not shift downward when you drag the formula to lower cells.

Solution 2

Use Data Cleaning Functions for Inconsistent Formatting

If your contact list has irregular capitalization or trailing spaces, you must nest TRIM and LOWER functions inside your matching formula to ensure accurate comparisons.

Efficient Data Matching

Compare and Clean Data Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions, array formulas, and logical operations like IF, ISNUMBER, and MATCH. You can quickly cross-reference large contact lists and clean up messy text formatting without any hassle.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the document containing your contact lists.
2. Select the Output Cell: Click the blank cell right next to your first contact's last name.
3. Enter the Match Formula: Input the `=IF(ISNUMBER(MATCH(...)))` formula into the formula bar and press Enter.
4. Auto-Fill the Column: Double-click the fill handle in the bottom-right corner of the active cell to instantly apply the matching logic to all rows.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in robust functions like TRIM and CLEAN to handle messy contact data flawlessly.Lightweight, fast, and completely free to use for both basic and advanced data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my MATCH formula returning #N/A even when the last name visually matches?

This usually occurs because of invisible trailing spaces, non-printable characters, or data type mismatches between the two columns. Using the TRIM or CLEAN functions inside your formula can resolve these discrepancies.

Can I use VLOOKUP instead of MATCH to find matching names?

Yes. You can achieve the exact same result using a formula like `=IF(ISNA(VLOOKUP(E2,$A$2:$A$640,1,FALSE)),"No","Yes")`. However, combining MATCH with ISNUMBER is often computationally faster and cleaner for simple existence checks.

How do I highlight the matching names instead of typing Yes or No?

You can use Conditional Formatting. Highlight your target column, navigate to 'Home' > 'Conditional Formatting' > 'New Rule', and select 'Use a formula to determine which cells to format'. Input `=ISNUMBER(MATCH(E2,$A$2:$A$640,0))` and set your preferred background fill color.

Excel & Data Problems