To find values in one Excel list that are missing from another, use FILTER with XMATCH; wrap the result in UNIQUE to remove repeated entries. For values that differ in either direction, combine the lists with VSTACK and filter out anything found in both.
What “unique values” means when comparing lists
In a list comparison, “unique” can mean two different things. It may mean values that appear in one list but not the other, or it may mean values that occur only once, even if the same value appears in both lists. These are different questions, so decide which result you need before choosing a formula.
This article treats a unique value as a value present in one list and absent from the other. For example, if List A contains Apple, Pear, Plum, Pear, and Kiwi, and List B contains Pear, Apple, and Mango, the values present in only one list are Plum, Kiwi, and Mango. Pear is shared, so it is not a difference—even though it appears more times in List A.
That distinction matters when repeated entries represent quantities, transactions, or individual records. A presence comparison checks whether a value appears at least once; it does not compare how many times it appears. If the number of occurrences matters, see the section on common problems below.
Find values in List A that are missing from List B
Assume List A is in cells A2:A6 and List B is in B2:B6, with the example values listed above. In an empty cell, enter this formula to return distinct values found in A but not B:
=UNIQUE(FILTER(A2:A6,ISNA(XMATCH(A2:A6,B2:B6)),"No A-only values"))
The result spills into the cells below the formula. With the example lists, it returns Plum and Kiwi, in the order they first appear in List A. The XMATCH function checks each value from A against List B. A missing match produces an error; ISNA turns that into a TRUE result for FILTER. Finally, UNIQUE prevents repeated unmatched entries from appearing more than once.
This formula requires Microsoft 365 or Excel 2021 or later for FILTER, UNIQUE, and XMATCH. If List A contains a value several times and it is absent from B, the formula still returns that value once. If every A value is present in B, the formula displays “No A-only values.”
Find values that differ in either direction
To return both A-only and B-only values in one result, use LET to name the ranges and VSTACK to make a combined list. With List A in A2:A6 and List B in B2:B6, enter:
=LET(a,A2:A6,b,B2:B6,all,VSTACK(a,b),UNIQUE(FILTER(all,(ISNA(XMATCH(all,a))+ISNA(XMATCH(all,b)))>0,"No differences")))
For the example, the result is Plum, Kiwi, and Mango. The formula tests every item in the combined list against both original lists. If an item is absent from either list, it is included; if it occurs in both, it is excluded. UNIQUE removes repeats, and the output follows the order in which values first appear in the combined list: first List A, then List B.
LET and VSTACK are available in Microsoft 365 or Excel 2024, while the formula also uses newer dynamic-array functions. If you have Excel 2021 but not the newer functions, use the separate one-direction formulas below and place the results in separate columns.
Set up the comparison step by step
- Put each list in its own column, with one value per cell and a header above each list if useful.
- Choose whether you need A-only values, B-only values, or differences in both directions.
- Enter the relevant formula in a blank cell outside the source lists. Leave enough empty cells below it for the results to spill.
- Check a few results against the source lists, especially if the data contains extra spaces, blank cells, or duplicates.
- If the lists will change, use a suitable range or Excel Tables so the comparison can include new entries.
For B-only values, reverse the roles of the ranges. With List A in A2:A6 and List B in B2:B6, enter =UNIQUE(FILTER(B2:B6,ISNA(XMATCH(B2:B6,A2:A6)),"No B-only values")). The example returns Mango. Keeping the two directions in separate output columns can make it easier to see which source list contains each difference.
Compare lists in older Excel versions
Excel 2019 and earlier do not include the same set of dynamic-array functions. A simple compatibility option is a helper column that labels each row according to whether its value is missing from the other list. With values in A2:A6 and B2:B6, enter this in C2 and fill it down through C6:
=IF(COUNTIF($B$2:$B$6,A2)=0,"Only in A","")
Rows labeled “Only in A” contain values that do not appear in B. To check the other direction, put the following in D2 and fill it down through D6:
=IF(COUNTIF($A$2:$A$6,B2)=0,"Only in B","")
This approach works in older Excel versions, but it reports each unmatched row. If A contains Plum twice, both rows are labeled. To produce a distinct list, filter the helper column for “Only in A,” copy the corresponding values, and use Data > Remove Duplicates on the copied results. Remove Duplicates changes the selected data, so apply it to a copy rather than the original list if you need to preserve every row.
Highlight differences without creating a result list
If you only need to inspect differences in place, conditional formatting can highlight values that are absent from the other column. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range starting at A2 and List B in B2:B100, use:
=COUNTIF($B$2:$B$100,A2)=0
Choose a format and confirm the rule. Excel highlights the A values that do not occur in B. To highlight B-only values, select B2:B100 and use =COUNTIF($A$2:$A$100,B2)=0. Adjust the ranges to match your actual data. This method marks source cells instead of returning a separate, spillable list, and it can show duplicate unmatched rows more than once.
Common comparison problems and how to avoid them
- Extra spaces: “Pear” and “Pear ” may not compare as expected. Clean imported data with
TRIMin a helper column, then compare the cleaned values. For nonbreaking spaces from copied web data, ordinary trimming may not remove every character. - Text versus numbers: A number stored as text, such as text “123,” may not behave like numeric 123 in every comparison workflow. Make both lists consistent: convert text-form numbers to numbers or format the source consistently as text before comparing.
- Blank cells: A fixed range may include empty cells. Select ranges that contain actual values, or remove blanks from the data before interpreting the results. Otherwise, blank entries can complicate a comparison.
- Repeated values: The dynamic-array formulas above return distinct unmatched values. They do not tell you how many times a value appears in each list. Use counts when occurrence frequency is important.
- Capitalization: Standard Excel matching functions such as
XMATCHandCOUNTIFare not case-sensitive. “pear” and “Pear” are treated as the same value. A case-sensitive comparison needs a different approach, such as a helper formula usingEXACT. - Wildcard characters:
COUNTIFtreats asterisks and question marks in criteria as wildcard characters. The mainXMATCHformulas use exact matching by default, which is preferable when those symbols are literal parts of text values.
If you actually need to compare occurrence counts, first count each value in both lists. For example, with a value in A2 and List B in B2:B6, =COUNTIF($B$2:$B$6,A2) returns how many times that value appears in B. Compare that count with its count in List A. A value appearing twice in A and once in B is shared by a presence check, but its occurrence counts differ.
Choose the method that fits the job
Use a dynamic-array formula when you want a live result list that updates as source values change. Use conditional formatting when you want to scan the original columns visually. Use helper columns when working with older Excel versions, auditing each row, or sharing a workbook with people who may not have newer functions.
For changing datasets, Excel Tables can make formulas easier to maintain. Select the source data and choose Insert > Table, or press Ctrl+T on Windows (Command+T on Mac in Excel). You can then refer to table columns by their names in formulas. When using tables, confirm that the formula refers to the intended columns and that added rows are included in the table.
The formulas here work in current Microsoft 365 Excel for Windows, and the same functions are available in Microsoft 365 for Mac and Excel for the web. The ribbon layout can differ slightly between platforms, but the formulas do not need changing. In Excel for the web, use the formula bar or a cell to enter a formula and allow the dynamic results to spill into adjacent cells.
Frequently Asked Questions
How do I find values in one Excel list but not another?
With List A in A2:A100 and List B in B2:B100, use =FILTER(A2:A100,ISNA(XMATCH(A2:A100,B2:B100)),"No A-only values"). Wrap the formula in UNIQUE if you want each unmatched value listed once.
How do I compare two lists and show all differences?
Use the LET, VSTACK, XMATCH, and FILTER formula in the both-directions section. It returns distinct values that appear in only one list, regardless of which list contains them.
Does Excel compare the number of duplicate entries?
No. The formulas shown check whether a value appears in each list, not whether it appears the same number of times. Compare counts separately if duplicate frequency matters.
Why does Excel say the formula is invalid?
Check that your Excel version supports the functions used, that the cell references match your layout, and that the formula uses commas as separators. Some regional Excel settings use semicolons as argument separators; if so, use the separator required by your installation.
Can I compare lists on different worksheets?
Yes. Replace a range with a sheet-qualified reference, such as Sheet2!A2:A100. If a worksheet name contains spaces, enclose it in apostrophes, as in 'January Data'!A2:A100.
Related
- Can You Change Keyboard Shortcuts in Excel?
- Can You Compare Excel Files? Methods and Limits
- Can You Change Capital Letters to Lowercase in Excel?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Days in Excel: Formulas for Dates and WorkdaysTo count calendar days between two dates in Excel, put the start date in A2 and the end…
- Excel LAMBDA vs LET: Differences, Uses, and ExamplesExcel’s LET function gives names to values inside one formula, while LAMBDA lets you define a reusable function…
- Excel Formula Between Two Numbers: ExamplesTo check whether a value in A2 is between a lower limit in B2 and an upper limit…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- Excel Average Days Between Dates: Formula and ExamplesTo average the number of days between paired dates in Excel, subtract each start date from its matching…
- SUMIF Excel Multiple Columns: Formulas and ExamplesTo use SUMIF with multiple columns, add separate SUMIF results for each value column, then combine them with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.