Function Problems

How to Find Matching Names in Two Excel Worksheets

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to identify and extract or highlight specific names that are present in two separate Excel worksheets.

Product: Excel
Device & OS: not provided
Scenario: Comparing lists of data (such as customer or patient names) located in column A of two different worksheets to find intersecting records.
Observed behavior: The names are split across multiple sheets, and the user requires a consolidated list, a visual highlight, or a status indicator to confirm which names exist in both places.

Before you start

Ensure that the names in both worksheets are formatted consistently without trailing spaces or spelling discrepancies, as formulas require exact text matches to work correctly.

Solution 1Recommended

Extract Matching Names Using FILTER and COUNTIF

Use this dynamic array formula to generate a clean, updated list of names that exist in both worksheets.

This method is highly efficient for Office 365 or newer spreadsheet versions that support dynamic arrays. It filters the first list based on a count of occurrences in the second list.

Select a destination cell

Click on an empty cell (e.g., C2) where you want the new list of matching names to spill over.

Enter the FILTER formula

Type the formula: =FILTER(A2:A15,COUNTIF('Sheet1 (2)'!A2:A18,A2:A15)>0)

Adjust the references

Modify the ranges (A2:A15) and worksheet names ('Sheet1 (2)') so they perfectly match the data ranges in your specific workbook.

Execute the formula

Press Enter to instantly generate the list of matched names.

Dynamic Arrays: Because FILTER is a dynamic array function, ensure there is enough empty space below the formula cell for the results to populate without encountering a #SPILL! error.

Solution 2

Highlight Matches Using Conditional Formatting

Ideal for visually spotting matching names directly within your original dataset without creating new columns.

Solution 3

Display Match Status Using VLOOKUP

Creates a helper column next to your data that explicitly states whether each individual name exists in the second sheet.

Work efficiently with WPS Spreadsheet

Find Matching Data Seamlessly in WPS Office

WPS Spreadsheet fully supports advanced functions like FILTER, COUNTIF, and VLOOKUP, making it incredibly easy to cross-reference data across multiple worksheets.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your two lists.
2. Input the array formula: Select a blank cell and input =FILTER(A2:A15,COUNTIF(Sheet2!A2:A18,A2:A15)>0) to extract the overlapping names.
3. Alternatively, use formatting: Highlight your data, navigate to Home > Conditional Formatting, and use the COUNTIF formula rule to color-code matching entries automatically.

100% compatibility with Microsoft Excel formulas and formattingFull support for dynamic arrays and advanced cross-sheet referencesIntuitive conditional formatting menus to visualize data quicklyFree and lightweight alternative for heavy data processing

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return #N/A even though the names look identical?

This is usually caused by hidden characters or extra leading/trailing spaces in one of the worksheets. You can wrap your cell references in the TRIM() function to clean the text before matching, or use the Find and Replace tool to remove extra spaces.

Can I find partial text matches between two worksheets?

Yes. You can use wildcard characters (like an asterisk *) in combination with COUNTIF or VLOOKUP. For example, using "*"&A1&"*" in your criteria will allow you to find if a partial string like 'John' exists within a full string like 'John Smith' on the other sheet.

How do I find matching values across three or more sheets?

To compare data across multiple sheets, you can nest multiple COUNTIF statements, consolidate the data into a single master sheet first using Power Query, or use a 3D reference array if the structure of all worksheets is identical.

Excel & Data Problems