iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use COUNTIF to mark whether each value appears in another list, FILTER with COUNTIF to produce a live list of missing values, and XLOOKUP when you need to return a related value or a not-found label. The right choice depends on the result you want, whether duplicates matter, and which Excel version you have.
Which Excel method should you use?
| Your goal | Start with | What it returns | Key limitation |
|---|---|---|---|
| Mark each row as present or missing | COUNTIF |
An occurrence count you can turn into a flag | Counts duplicates; text comparisons are case-insensitive. Microsoft Support |
| List every item in one range that is absent from the other | FILTER + COUNTIF |
A dynamic list of exceptions | FILTER is not available in every Excel edition. Microsoft Support |
| Find a match and return a related value or a not-found message | XLOOKUP |
A value from a corresponding return range | Unavailable in Excel 2016 and Excel 2019. Microsoft Support |
| Compare whole workbook files or versions | Spreadsheet Compare | A report of differences between workbooks | Availability is limited to specified Office Professional Plus editions and Microsoft 365 Apps for enterprise. Microsoft Support |
These methods answer different questions: COUNTIF tells you how many matches there are, FILTER returns the items that meet a condition, and XLOOKUP returns a corresponding value. They do not all prove that two lists are identical.
Set up the ranges and decide what counts as a match
The examples below assume List A is in A2:A100 and List B is in B2:B100. Adjust those ranges to fit your sheet. Check that both lists use consistent formats: for example, a number stored as text may not behave like the same number stored numerically.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Before comparing, decide whether you care about simple membership, duplicate counts, row-by-row correspondence, or a related field such as an amount or status. A membership check treats an item as present if it appears at least once. If List A contains the same name three times and List B contains it once, a membership test marks all three occurrences as found; it does not reconcile duplicate frequency.
Mark each value in one list as found or missing with COUNTIF
To check each entry in List A against List B, enter this formula in C2 and fill it down:
=IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing")COUNTIF(range,criteria) counts cells in a range that meet a criterion. Here, it counts how many times the value in A2 occurs in List B. A zero count produces “Missing”; a positive count produces “Found.” See Microsoft’s COUNTIF documentation.
To check List B against List A instead, swap the ranges: use A2:A100 as the range being checked and the value from column B as the criterion. This is how to compare lists in both directions rather than finding exceptions in just one.
Filter the helper results to see exceptions
If your Excel version does not support FILTER, the helper column is a practical fallback. Apply the worksheet filter to the status column and show only “Missing.” This lets you review the exceptions without changing either source list.
Return a live list of missing items with FILTER and COUNTIF
In an Excel edition that supports FILTER and dynamic array results, use this formula to return entries from List A that do not occur 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 condition =0 identifies entries absent from List B, and FILTER returns those entries. The third argument displays “No missing items” when nothing meets the condition. Microsoft documents FILTER‘s include array and optional empty-result argument in its FILTER function reference; this combined formula applies the documented functions together.
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
To find items in List B that are missing from List A, reverse the ranges:
=FILTER(B2:B100,COUNTIF(A2:A100,B2:B100)=0,"No missing items")The result spills into cells below the formula. Leave that area clear so Excel can display the full result; a blocked spill area can prevent the list from appearing.
Return a matched value or a not-found label with XLOOKUP
When you need a related value from the matching row, use XLOOKUP. To check whether a value in A2 appears in List B and display a label if it does not, enter:
=XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,"Missing",0)The lookup array and return array are both List B here, so a match returns the matched entry. The optional "Missing" argument supplies a readable result when no match is found; 0 requests exact matching. Exact match is also the default. Microsoft explains that XLOOKUP uses separate lookup and return arrays in its function documentation.
For a real reconciliation, the return range is often a different column. For example, if names are in B2:B100 and their statuses are in C2:C100, use C2:C100 as the return array to retrieve the status associated with the match.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Check your Excel version before using newer functions
Microsoft’s XLOOKUP reference says the function is unavailable in Excel 2016 and Excel 2019, although those versions may open a workbook containing a formula authored in a newer version. Microsoft’s FILTER reference lists Microsoft 365, Excel 2024, Excel 2021, and mobile editions. Availability can depend on platform and license, so check the documentation for your installed edition if a function is missing.
Best Value
If your version lacks FILTER, use the COUNTIF helper column and filter the status results. If it lacks XLOOKUP, a helper count can still identify present and missing entries, though it will not return a related field by itself.
Account for case, wildcards, and duplicate data
Case and wildcard characters
COUNTIF text criteria are case-insensitive: it treats “Lee” and “LEE” as a match. Its criteria also recognize * and ? as wildcard characters. If capitalization is meaningful for your identifiers, or those characters should be matched literally, this basic COUNTIF pattern is not sufficient; use a formula designed for case-sensitive or literal-character comparison. Microsoft’s COUNTIF guidance also notes that criteria strings longer than 255 characters can return incorrect results.
Duplicates and one-to-one matching
A positive COUNTIF result answers whether at least one occurrence exists, not whether both lists contain the same number of each item. If the task requires matching duplicate quantities or pairing rows one-to-one, define that requirement before relying on a membership formula; the patterns above do not establish equal duplicate distributions.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC 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 & 11Do not remove duplicates just to make a comparison easier unless that is truly the desired cleanup. Microsoft says filtering for unique values hides duplicates temporarily, while Remove Duplicates permanently deletes them from the selected cells or table. Keep a copy of the source data before using that operation. See Microsoft’s duplicate guidance.
When you need to compare entire workbooks
If the differences you need are between whole workbook files or versions—not just two columns—Microsoft describes Spreadsheet Compare as a way to produce a report of differences and potential problems. Its availability is edition-limited: Microsoft’s page lists Office Professional Plus 2013, 2016, 2019, and 2021, or Microsoft 365 Apps for enterprise. Check the Spreadsheet Compare support page for details.
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.
