To compare two columns as unordered lists—not by checking cells on the same row—test whether each value in one list appears anywhere in the other. With values in A2:A6 and B2:B6, enter this in C2 and fill down: =IF(A2="","",IF(COUNTIF($B$2:$B$6,A2)=0,"Only in A","Also in B")). It labels each nonblank value from column A according to whether it appears anywhere in column B, regardless of row order. You can repeat the check in the other direction to find values that appear only in B.
This approach is useful for comparing two lists such as product codes, names, or order numbers when their entries may be sorted differently. It checks membership, not whether the values in A2 and B2 match. Before starting, decide whether you want to find missing items, highlight matches, or check duplicate counts; those are related but different comparisons.
What You’ll Need
- An Excel workbook with the two lists in separate columns. The examples use columns A and B, with headers in row 1 and data beginning in row 2.
- A spare column for results, such as column C, if you want a label beside each value.
- Excel for Microsoft 365 on Windows. The COUNTIF and conditional-formatting methods below also work in many earlier desktop versions. Instructions for Excel for the web and Mac are noted where relevant.
Step-by-Step
- Check what kind of comparison you need. For an unordered-list comparison, each value should be searched for anywhere in the other column. For example, if column A contains Apple, Pear, Plum, Kiwi, and Mango, and column B contains Mango, Apple, Grape, Pear, and Kiwi, the matching values are Apple, Pear, Kiwi, and Mango. Plum appears only in A and Grape only in B. The fact that Plum and Grape share a row does not make them a match. Also decide whether repeated entries matter: the basic presence test tells you whether a value exists at least once, not whether the lists contain the same number of copies.
- Check the data and set your comparison range. Make sure both lists have headers in row 1 and values starting in row 2, or adjust the formulas to the actual first and last data rows. Remove any obvious extra spaces, confirm that identifiers are stored consistently, and avoid including totals or notes below a list. The examples use rows 2 through 6, so the formula searches only those five entries. For longer lists, replace row 6 with the last relevant row, such as row 500.
- Add a result column for values in A. Put a heading such as Status A in C1. With values in A2:A6 and B2:B6, enter the following formula in C2. The formula leaves an empty A cell blank, then reports whether its value occurs in B2:B6:
=IF(A2="","",IF(COUNTIF($B$2:$B$6,A2)=0,"Only in A","Also in B")). In the example lists, C2 for Apple says Also in B, C3 for Pear says Also in B, C4 for Plum says Only in A, C5 for Kiwi says Also in B, and C6 for Mango says Also in B. - Fill the formula down the list. Select C2, then drag the fill handle—the small square at the lower-right corner of the cell—down to C6. You can also copy C2, select C3:C6, and paste. The references to A2 change as the formula moves down, while the dollar signs keep the search range B2:B6 fixed. If your list has hundreds of rows, fill the formula through the final data row rather than stopping at row 6.
- Check the other direction. A one-way check does not show which values exist only in B. To identify those, place a heading such as Status B in D1. With values in A2:A6 and B2:B6, enter this formula in D2 and fill it down through D6:
=IF(B2="","",IF(COUNTIF($A$2:$A$6,B2)=0,"Only in B","Also in A")). In the example, D4 labels Grape as Only in B. Checking both directions gives you the full difference between the two lists. - Filter the results to focus on differences. Select your data and result columns, then choose Home > Sort & Filter > Filter. Use the filter arrow in Status A to show Only in A; use the arrow in Status B to show Only in B. You can clear a filter from the same menu when you want to see the full lists again. Filtering hides rows temporarily; it does not delete or change the underlying values.
- Optionally highlight values that are missing from the other list. Select A2:A6, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter the formula below, choose Format to set a fill color, and select OK. With A2:A6 selected and B2:B6 as the comparison range, use:
=AND(A2<>"",COUNTIF($B$2:$B$6,A2)=0). A2 must refer to the first cell in the selected range; the B range stays fixed. To highlight values missing from A, select B2:B6 and create a second rule. With those selections, use:=AND(B2<>"",COUNTIF($A$2:$A$6,B2)=0). On Mac, the same Conditional Formatting commands are available from the Home tab, though some dialog layouts may differ. Excel for the web also supports conditional formatting, but the rule dialog can look different from desktop Excel. - Review the result before sharing it. Check a few labels against the original lists, especially values near the top and bottom of the ranges. If you add more data later, extend the formula and conditional-formatting ranges. Consider turning the lists into Excel tables with Home > Format as Table if they will grow regularly; table formulas can fill down automatically, but check the references after converting your existing setup.
Common Mistakes
- Comparing row by row by accident. A formula such as
=A2=B2checks only those two cells. It will mark out-of-order matches as different. Use COUNTIF with the other column as its search range to check for a match anywhere in that list. - Checking only one direction. Searching for every A value in B finds A-only entries, but it does not identify B-only entries. Run the reverse check as well when you need a complete comparison.
- Using different range sizes or missing the last row. If one list goes to row 200 but the formula stops at row 100, values below row 100 are ignored. Confirm both ranges cover all the intended data, and keep the result formula filled down to the last item.
- Assuming a match means equal quantities. If Apple appears three times in A and once in B, the membership formula still reports that Apple is present in B. It does not compare how many times each item occurs.
- Overlooking spaces, data types, and wildcards. A value with a leading or trailing space may not match the version without it. Numbers stored as text can also behave differently from numeric values in some comparisons. COUNTIF treats asterisks and question marks in its criteria as wildcard characters, so entries containing those symbols can produce unexpected results.
Tips
- Keep the dollar signs in the search range. In
$B$2:$B$6, the dollar signs lock both ends of the range when you fill the formula down. Without them, the search area would move from row to row and could miss earlier entries. - Use clear labels. Labels such as Only in A and Also in B are easier to filter and review than a bare TRUE or FALSE. If you prefer a compact result, you can change the text in the formula while keeping the test itself unchanged.
- Trim inconsistent spaces in a helper column. If values may contain extra spaces, create a cleaned copy with TRIM before comparing. With the original text in A2, enter
=TRIM(A2)in a spare column and fill down; repeat for B. Compare the cleaned columns and keep the originals if you need to preserve the source data. TRIM removes ordinary extra spaces but does not remove every kind of invisible character. - Choose a case-sensitive test only when needed. COUNTIF does not distinguish between uppercase and lowercase letters. To require an exact case-sensitive text match, use EXACT within SUMPRODUCT. With the value to check in A2 and the comparison list in B2:B6, enter this in C2 and fill down:
=IF(A2="","",IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$6))=0,"Only in A","Also in B")). For example, Apple and APPLE count as different under this test. EXACT also avoids COUNTIF’s wildcard interpretation, but use it only if case sensitivity is part of your requirement. - Compare duplicate counts when the quantity matters. With values in A2:A6 and B2:B6, enter this in C2 and fill down to see how many more copies of that value appear in A than in B:
=COUNTIF($A$2:$A$6,A2)-COUNTIF($B$2:$B$6,A2). A result of 0 means the counts are equal, 1 means A has one extra occurrence, and -1 means B has one extra occurrence. For the example lists, Plum returns 1 and Apple returns 0. This shows the difference for each row’s value; repeated values can therefore produce repeated results. - Use a consistent data type for IDs and dates. If one list contains a number stored as text and the other contains a real number, convert one list so both use the same type before interpreting the results. For dates, check that the underlying values are actual Excel dates rather than text that merely looks like a date.
Troubleshooting
- The result says Only in A even though the value looks present in B. Compare the two cells in the formula bar, not just their displayed appearance. Look for spaces, nonprinting characters, different punctuation, or a number-versus-text mismatch. Try a TRIM helper column for ordinary extra spaces. If the entries contain * or ?, COUNTIF may treat those as wildcards; use the case-sensitive EXACT and SUMPRODUCT formula above when literal character matching is needed.
- Some rows show the same result after filling down. Select a few cells in the result column and inspect their formulas. The reference to the current value should change from A2 to A3 and onward, while the comparison range should remain $B$2:$B$6. If the whole formula was typed with $A$2, it will keep checking the same source value; correct the reference and fill down again.
- New items are not being detected. Check whether the comparison range includes the newly added rows. A formula set to $B$2:$B$100 will not search below row 100. Extend the range and copy the formula down. If your lists grow often, consider converting them into tables so new rows are easier to include.
- Blank rows show a match or an unexpected label. Use the outer IF check shown in the examples so a blank in the source column returns a blank result. If a cell appears empty but still receives a label, it may contain a space or a formula returning text; clear it or clean the value before comparing.
- The formula appears as text instead of calculating. Change the result cell format to General, then select the cell, press F2, and press Enter. Also confirm that the formula begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut may vary by keyboard and Excel settings, so use Formulas > Show Formulas to check.
Frequently Asked Questions
Does this compare values on the same row?
No. COUNTIF searches the entire specified range in the other column. It can find Apple in B even if Apple is on a different row from its entry in A.
How do I find values that are in both columns?
Use the same presence formula and look for Also in B. That label means the value from column A appears somewhere in column B. To identify shared values from B as well, review the reverse-check column.
Will the formula find every duplicate?
It will label each occurrence based on whether the value appears at least once in the other list. It does not compare duplicate quantities. Use the COUNTIF difference formula in Tips if you need to compare counts.
Is COUNTIF case-sensitive?
No. It treats Apple and APPLE as a match. Use the EXACT and SUMPRODUCT alternative in Tips if uppercase and lowercase must be treated as different.
Can I compare columns on a Mac or in Excel for the web?
Yes. The COUNTIF formulas work in Microsoft 365 for Mac and Excel for the web, with the same comma-separated syntax. Conditional-formatting dialog details can vary, but the formula-based rule uses the same logic.
Can I compare two columns without adding a result column?
Yes. Apply the conditional-formatting rule to the original list to highlight items that are missing from the other column. A result column is preferable when you want labels you can filter, sort, or review later.
Related
- Adding a Checkbox to Excel: Setup, Linking, and Tips
- Average Growth Rate in Excel: Calculate It Step by Step
- How to Calculate an Average in an Excel Column
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Import Emails From Outlook: PST and Account OptionsTo import emails into Outlook, use Outlook classic for Windows to import a Personal Folders file (.pst): choose…
- Remove a Dropdown in Google Sheets Without Deleting DataTo remove a dropdown in Google Sheets, select the cell or range, open Data > Data validation, and…
- Remove Duplicates in Excel: How It Works and What to KnowIn Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates. Excel compares…
- Find Duplicates in Excel: A Practical Step-by-Step GuideTo find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional…
- Google Sheets Filter Views: Create, Use, and Share ThemA Google Sheets filter view is a saved way to sort or filter a table without changing the…
- How to Retrieve Archived Emails in Outlook on Any DeviceTo retrieve an archived email in Outlook, open the Archive folder, find the message, then move it back…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.