Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11To compare two lists in Excel, use COUNTIF to mark whether each item appears in the other list, FILTER with COUNTIF to spill a list of missing items, or XLOOKUP when you need a corresponding value or a clear “Missing” result. The right choice depends on whether you need a flag, an exceptions list, or a returned field—and whether your Excel version supports the function.
Choose the result you need
| Your goal | Start with | What it returns | Key limitation |
|---|---|---|---|
| Mark each item as present or missing | COUNTIF |
An occurrence count that you can turn into a flag | Counts duplicates; text criteria are case-insensitive. Microsoft’s COUNTIF reference |
| List all items in one range that are absent from another | FILTER + COUNTIF |
A dynamically filtered list of exceptions | FILTER is limited to supported modern Excel editions. Microsoft’s FILTER reference |
| Find a related value or display a not-found label | XLOOKUP |
A value from a corresponding return range, or a specified fallback | Not available in Excel 2016 or Excel 2019. Microsoft’s XLOOKUP reference |
| Compare two entire workbook files | Spreadsheet Compare | A report of differences between workbooks or versions | Availability is limited to specified Office Professional Plus editions or Microsoft 365 Apps for enterprise. Microsoft’s Spreadsheet Compare guidance |
These methods answer different questions: COUNTIF says how many times an item occurs; FILTER returns the items that meet a condition; XLOOKUP returns a corresponding value. Decide whether “same” means present at least once, present the same number of times, or paired to a specific row before choosing a formula.
As an Amazon Associate I earn from qualifying purchases.
Flag values in one list as found or missing
Suppose List A is in A2:A100 and List B is in B2:B100. In C2, enter this formula and fill it down alongside List A:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing")COUNTIF(range,criteria) counts cells in the range that meet the criterion. Here it checks how often the value in A2 occurs in List B. A zero count produces “Missing”; a positive count produces “Found.” The dollar signs keep the checked range fixed as you fill the formula down.
To check List B against List A, reverse the ranges: place the formula beside List B and use =IF(COUNTIF($A$2:$A$100,B2)>0,"Found","Missing"). Checking in both directions reveals items unique to either list.
Spill a list of missing items
If your Excel edition supports FILTER, this formula returns the entries from List A that have no occurrence in List B:
=FILTER(A2:A100,COUNTIF(B2:B100,A2:A100)=0,"No missing items")COUNTIF supplies a count for each value in List A; the test =0 forms the include condition for FILTER. The final argument displays “No missing items” if no entries qualify. To list values in B absent from A, swap the source and checked ranges:
=FILTER(B2:B100,COUNTIF(A2:A100,B2:B100)=0,"No missing items")The combined formula applies the documented behavior of both functions; it is not a quoted Microsoft example. If the formula is rejected, check that your edition supports FILTER and whether your Excel locale uses different formula argument separators. Without FILTER, use the COUNTIF flag column, then filter that worksheet column to show only “Missing.”
Rank #3
Use XLOOKUP when you need a matched value
To check whether the value in A2 appears in List B and return either the match or a label, use:
=XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,"Missing",0)The lookup range and return range are both List B here, so a found item is returned as-is. The optional “Missing” argument supplies the not-found result, and 0 specifies an exact match; exact match is also the default. For reconciliation, point the return range to the associated field instead—for example, a status or amount column aligned row-for-row with the lookup range. Microsoft describes the distinction this way: “XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number.” Microsoft Support
Check Excel compatibility before choosing a formula
XLOOKUPis unavailable in Excel 2016 and Excel 2019, according to Microsoft’s function reference. A workbook containing it may still open in those versions if it was authored in a newer version, but that does not make the function available for use there. XLOOKUP availability- Microsoft’s
FILTERreference lists Microsoft 365, Excel 2024, Excel 2021, and mobile editions. Availability can depend on platform, language, and license, so verify the installed edition if uncertain. FILTER availability - For other function-version markers, consult Microsoft’s Excel functions by category reference.
Account for duplicates, case, and special characters
Membership is not a duplicate-count comparison
The flag formula tests whether an item appears at least once. If List A contains “Orchid” three times and List B contains it once, all three A rows are marked “Found.” That does not establish that the lists have the same duplicate frequencies or that each row has a one-to-one counterpart. If counts or quantities matter, compare occurrence totals or reconcile records using an appropriate unique identifier.
COUNTIF ignores text case
Ordinary COUNTIF text criteria are case-insensitive, so values differing only by uppercase and lowercase letters are treated as matching. If case is meaningful—for example, in a case-sensitive identifier—the basic formula is not sufficient.
Asterisks and question marks act as wildcards
In COUNTIF criteria, * can represent any sequence of characters and ? can represent one character. If those symbols occur literally in your data and must not act as wildcards, use a more specific approach than the basic membership formula. Microsoft also warns that criteria strings longer than 255 characters can return incorrect results. COUNTIF details and troubleshooting
Keep the check separate from duplicate cleanup
Comparison formulas help identify what is missing; they do not require deleting or changing source data. If you choose to remove duplicates afterward, first keep a copy of the source. Microsoft distinguishes filtering unique values, which only hides duplicates temporarily, from Remove Duplicates, which permanently deletes duplicate values from the selected cells or table. Microsoft’s duplicate guidance
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
