This guide shows you how to compare two columns where each cell contains multiple values separated by commas, such as “apple, banana, cherry”. You’ll learn methods to find matches, differences, and values unique to one column, using formulas and Excel’s built-in tools. No prior advanced Excel knowledge is needed, but you should be comfortable with basic formulas and cell references.
What You’ll Need
- Microsoft Excel (2016 or later, including Microsoft 365) or Google Sheets (the formulas work in both).
- Two columns of data with comma-separated values, e.g., Column A and Column B.
- A blank column (or a new sheet) to place the comparison results.
- Basic familiarity with entering formulas and dragging fill handles.
Step-by-Step
Method 1: Check if Any Value in One Cell Appears in the Other Cell
This method tells you whether at least one item from the first cell exists in the second cell. It’s useful for a quick yes/no answer.
- Assume your data is in columns A and B, starting at row 2 (row 1 has headers).
- In cell C2, enter the following formula:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*100+1,100))),B2)))>0,"Match","No Match")This formula splits A2 by commas, checks each piece against B2, and returns “Match” if any piece is found.
- Press Enter. You’ll see “Match” if any value from A2 appears in B2, otherwise “No Match”.
- Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to the rest of the rows.
Method 2: Extract Values That Are Common to Both Cells
This method lists the individual items that appear in both cells, separated by commas.
- In cell D2, enter this array formula (in Excel 365, it works normally; in older versions, press Ctrl+Shift+Enter):
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*100+1,100))),B2)),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*100+1,100)),""))This returns a comma-separated list of items from A2 that also appear in B2.
- Press Enter. If there are common items, they appear; if not, the cell shows blank.
- Drag down to apply to other rows.
Method 3: Highlight Differences Using Conditional Formatting
This visual method highlights cells that contain values not present in the other column.
- Select the range in column A (e.g., A2:A10).
- Go to the Home tab, click Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter this formula (adjust $B2 to your second column):
=SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))-1)*100+1,100))),$B2)))=0This formats A2 if none of its values appear in B2.
- Click Format, choose a fill color (e.g., yellow), and click OK.
- Repeat the same steps for column B, using a similar formula but swapping $A2 and $B2.
Method 4: Use Power Query (for Large Datasets)
If you have thousands of rows, Power Query can split and compare more efficiently.
- Select your data range (including headers).
- Go to Data > From Table/Range (if not already a table, Excel will create one).
- In Power Query Editor, select the column with comma-separated values.
- Go to Home > Split Column > By Delimiter. Choose Comma, and split into rows (or columns, depending on your need).
- Repeat for the other column.
- Use Merge Queries to find matches, or use Remove Duplicates to find differences.
- Load the result back to a worksheet.
Common Mistakes
- Forgetting to trim spaces: If your data has spaces after commas (like “apple, banana”), the SEARCH function may not match because of leading spaces. Always use TRIM in the formula.
- Using SEARCH without splitting: SEARCH finds substrings, so “apple” would match “pineapple”. Splitting first prevents false positives.
- Not using array formula in older Excel: If you don’t press Ctrl+Shift+Enter in Excel 2019 or earlier, the formula may return #VALUE! or an incorrect result.
- Assuming case sensitivity: SEARCH is case-insensitive. If you need case-sensitive comparison, use FIND instead.
Tips
- Use TRIM and CLEAN to remove extra spaces and non-printable characters from your data before comparing.
- If your data has a consistent format, consider using Text to Columns (Data tab) to split by comma into separate columns, then use VLOOKUP or COUNTIF to compare.
- For a quick visual check, use Conditional Formatting with the formula method—it’s less intimidating than long formulas.
- In Excel 365, you can use the TEXTSPLIT function to split values more easily. For example,
=TEXTSPLIT(A2,",")returns an array of items. - Test your formula on a small sample first to ensure it works before applying to the entire dataset.
Troubleshooting
- Formula returns #VALUE!: This often happens when the formula is not entered as an array formula (in older Excel). Press Ctrl+Shift+Enter after typing the formula. Also check that your data doesn’t contain errors.
- Formula returns #NAME?: This means you’re using a function not available in your Excel version (e.g., TEXTJOIN in Excel 2016). Use a different method or upgrade to Microsoft 365.
- No matches found even though you know there are some: Check for leading/trailing spaces or different case. Use TRIM and maybe convert both to lowercase with LOWER.
- The formula is too slow: The SUMPRODUCT with INDIRECT is resource-heavy. If you have more than a few thousand rows, use Power Query instead.
- Conditional Formatting not highlighting correctly: Ensure the formula is relative to the active cell in the selection. The formula should reference the first cell in the selected range (e.g., A2) without absolute column/row signs where needed.
Frequently Asked Questions
Can I compare more than two columns?
Yes, you can extend the formulas to compare multiple columns by nesting additional SEARCH functions or using a helper column to combine values first.
How do I compare ignoring case?
Use SEARCH (case-insensitive) instead of FIND. If you need case-sensitive, replace SEARCH with FIND in the formulas.
What if my delimiter is not a comma (e.g., semicolon or pipe)?
Replace the comma in the SUBSTITUTE function with your delimiter. For example, use SUBSTITUTE(A2,”;”,REPT(” “,100)) for semicolons.
Can I compare two columns in different sheets?
Yes, just reference the other sheet in the formula, e.g., Sheet2!$B2 instead of $B2.
Is there a way to compare without formulas?
Yes, you can use Power Query (Get & Transform) to split and merge columns, or use the “Remove Duplicates” feature after combining columns, but that’s less precise for partial matches.
Why does my formula return “Match” when it shouldn’t?
This can happen if one value is a substring of another (e.g., “cat” matches “category”). To avoid this, use a more precise split and compare with exact match (e.g., using ISNUMBER(SEARCH(” “&value&” “, ” “&B2&” “))).
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- How to Turn Off Shutter Sound on iPhone
- VPN Setup on iPhone SE: Step-by-Step Guide
- Fix Internet Not Working on Windows 11
- Fix Right Click Not Working in Firefox
- How to Limit Ad Tracking on iPhone Apps
- WiFi Adapter Driver: Find & Install the Right One