How to Find Matching Numbers in Two Excel Cells
Question details
The user needs to compare two comma-separated lists contained within two different cells and extract the exact numbers that appear in both lists.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two cells containing lists of numbers to find their intersection or common values without returning false partial matches.
- Observed behavior
- A combined comma-separated list of matching values is returned, ensuring that numbers are matched as complete units (e.g., matching 12 but not incorrectly matching it inside 112).
Ensure your Excel version supports Dynamic Array functions like TEXTSPLIT and FILTER, which are generally available in Excel 365 and Excel 2021. For older versions, the Power Query method is recommended.
Use a Dynamic Array Formula to Find Matches
Use the LET, TEXTSPLIT, and FILTER functions to split the lists, compare the values, and find exact matching numbers.
This method involves splitting the text string in the first cell into an array and using the SEARCH function to see if those numbers exist in the second cell. Commas are added around the search strings to guarantee that only whole numbers are matched.
Click on an empty cell where you want the matching numbers to appear (e.g., cell B2).
Assuming your lists are in cells A2 and C2, enter the following formula: =LET(a,TEXTSPLIT(A2,","),TEXTJOIN(",",TRUE,FILTER(a,ISNUMBER(SEARCH(","&a&",",","&C2&",")),"")))
Press Enter. The formula will instantly split the text, search for exact number matches, and join the common values into a single comma-separated text string.
Compare Lists Using Power Query
Use Power Query to split data and perform an Inner Join to extract common values, which is ideal for older Excel versions or massive datasets.
Compare and Find Matching Data in WPS Office
WPS Spreadsheet offers powerful data processing capabilities, including advanced text functions and dynamic arrays. You can easily compare lists and extract matching values with the same familiar formulas.
- 1. Open your dataset: Launch WPS Office and open the spreadsheet containing the comma-separated data lists.
- 2. Select a target cell: Click on a blank cell next to your lists where you want the intersection to be displayed.
- 3. Apply the formula: Input the combined TEXTSPLIT and FILTER formula to identify the identical values across the cells.
- 4. Generate matches: Press Enter to instantly generate a comma-separated list of perfectly matched numbers.
Frequently Asked Questions
Why does my formula return a #NAME? error?
The #NAME? error usually occurs if you are using an older version of Excel that does not support modern dynamic array functions like TEXTSPLIT or LET. Consider upgrading your Office version or use the Power Query method instead.
Can I compare cells separated by spaces instead of commas?
Yes, you can easily modify the delimiter in the formula. Replace the comma (",") with a space (" ") in both the TEXTSPLIT arguments and the SEARCH concatenation arguments.
How do I highlight matching numbers instead of extracting them into a new cell?
If your numbers are in individual cells rather than comma-separated lists, you can use Conditional Formatting. Select your range, choose 'Highlight Cells Rules' > 'Duplicate Values', and the software will color-code numbers that appear in both ranges.
