To return values that appear in both Excel lists without repeating them, use =UNIQUE(FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(XMATCH(A2:A100,B2:B100)),"")). With the first list in A2:A100 and the second in B2:B100, the formula spills a distinct list of nonblank matches; it requires Microsoft 365 or Excel 2021 or later.
Get the quickest exact-match result
Put the formula in an empty cell outside the two source lists, such as D2. Excel fills the cells below it with the results, so leave enough room for the output and avoid placing it inside an Excel Table. If the sample list in column A contains Red, Blue, Red, Gold, Green, and Blue, and column B contains Blue, Red, Blue, and White, the result is Red and Blue, in the order they first occur in column A. Repeated values in either list do not cause repeated output.
The formula compares complete cell values, not fragments. Red matches Red, but it will not match Dark Red. Matching text is generally case-insensitive in Excel, so red and Red are treated as a match. The formula also excludes blank cells in column A, which helps prevent an empty entry appearing in the results. It does not change or remove anything in the original lists.
For a real workbook, replace the example ranges with ranges that cover your data. Keep the two lists in separate columns and use similarly sized ranges where practical. A bounded range such as A2:A500 is often easier to manage than referencing entire columns in a calculation that performs many comparisons.
How the formula works
The formula combines three functions. XMATCH checks each value from the first list against the second list. ISNUMBER turns a successful match position into TRUE and a missing match into FALSE. FILTER keeps only rows marked TRUE, while the additional nonblank test removes empty source cells. UNIQUE then returns each remaining value once.
With names in A2:A100 and comparison names in B2:B100, this formula returns matching names once each, even if a name appears multiple times in either column: =UNIQUE(FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(XMATCH(A2:A100,B2:B100)),"")). The asterisk combines the two conditions as an AND test: the source cell must not be blank and must be found in the other list. The empty-string argument at the end of FILTER tells Excel what to show if nothing qualifies.
UNIQUE keeps the order of first appearance in the filtered source list; it does not sort alphabetically. If you want a sorted result, wrap the expression in SORT. With the same lists in A2:A100 and B2:B100, this formula sorts the distinct matches: =SORT(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(XMATCH(A2:A100,B2:B100)),""))). Sorting can be helpful for review, but it no longer reflects the sequence of the first list.
Decide what “without duplicates” means
People use “match without duplicates” for two different tasks. The formula above produces a distinct set of shared values: each matching value is listed once. It does not report how many times that value occurs, and it does not pair each occurrence in one list with a separate occurrence in the other.
If you need every matching row from the first list, including repeats, use FILTER without UNIQUE. With source values in A2:A100 and lookup values in B2:B100, the following returns all nonblank rows from A that have a match in B: =FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(XMATCH(A2:A100,B2:B100)),""). If Red occurs three times in A and at least once in B, Red appears three times in this result. That is useful when each row represents a transaction, order, or person-record that must remain separate.
To show a count beside a distinct match, first place the distinct-match formula in D2. With the comparison list in B2:B100 and a returned value in D2, enter this in E2 and fill it down: =COUNTIF($B$2:$B$100,D2). The number is the count of that value in column B. For example, if Blue occurs three times in B, the count beside Blue is 3. This is a count of occurrences, not a one-to-one reconciliation of rows.
Choose the right method for your data
- Need a clean list of shared items? Use UNIQUE with FILTER and XMATCH. This is the most direct option when each value should appear once.
- Need matching source rows, including repeats? Use FILTER without UNIQUE so duplicate records remain visible.
- Need to mark matches beside the original data? Use a helper column with COUNTIF. With a value in A2 and the comparison list in B2:B100, enter
=IF(AND(A2<>"",COUNTIF($B$2:$B$100,A2)>0),"Match","")in a helper cell on row 2, then fill it down. It displays Match when A2 is nonblank and occurs in the comparison range. - Need counts or reconciliation? Add a count beside each value, or use a more specific record key if names alone do not uniquely identify records.
COUNTIF and XMATCH test whether a value exists at least once; neither automatically pairs the first occurrence in one list with the first occurrence in another. For occurrence-by-occurrence reconciliation, consider adding an occurrence number for each repeated key or matching on a genuinely unique identifier, such as an order number. A name by itself may not be unique enough to identify a person or transaction.
Use an option in older Excel versions
UNIQUE, FILTER, and XMATCH are available in Microsoft 365 and Excel 2021 or later, but not in every older Excel release. If your version does not recognize them, a helper column gives you a practical way to identify matches. With source values in A2:A100 and comparison values in B2:B100, put =IF(AND(A2<>"",COUNTIF($B$2:$B$100,A2)>0),"Match","") beside the first source row and copy it down. Filter the helper column to show Match, then copy those source values to a separate area.
To make that copied list distinct in Windows desktop Excel, select the copied values and use Data > Remove Duplicates. This command removes duplicate entries from the selected data, so work on a copy rather than the original source. Another built-in route is Data > Advanced in the Sort & Filter group, where the Unique records only option can create a distinct copy of a selected range. These tools remove repeated values from a selected result; they do not themselves determine which entries overlap between two lists, which is why the helper test comes first.
Excel for Mac has the same core formulas in supported Microsoft 365 and Excel 2021 releases, although ribbon placement can vary slightly by build. In Excel for the web, dynamic-array formulas are also available, but some desktop commands and interface details differ. If formulas display errors after being pasted, check your regional list separator: some Excel installations expect semicolons instead of commas. Use the separator Excel accepts in your installation.
Fix common matching problems
A value that looks identical may not be identical to Excel. One list might contain a trailing space, a nonbreaking space copied from a web page, or a number stored as text while the other contains a numeric value. Check a few unmatched examples in the formula bar, and make sure both lists use consistent data types and formatting. Number formatting changes how a value looks, but it does not always change the underlying value.
- Unexpected blanks: The main formula excludes blanks from the first list. If blank values have a meaningful role in your data, decide explicitly whether they should count as matches rather than allowing them in by accident.
- Missing expected text matches: Check for extra spaces, punctuation differences, and inconsistent abbreviations. Exact matching does not treat “St.” and “Street” as the same value.
- Too many results: Confirm that you are comparing the intended columns and that the formula ranges do not include unrelated data or old entries below the current list.
- #SPILL!: Clear cells in the output area that block the results. Also check whether the formula was entered inside a Table; spilled-array formulas need room in the worksheet grid.
- Formula not recognized: Your Excel version may not support one of the dynamic-array functions, or your locale may require different argument separators.
For text cleanup, make a cleaned helper column rather than editing source data blindly. TRIM can remove ordinary extra spaces at the beginning and end of text, but it does not remove every kind of invisible character. If the lists come from an external system, compare sample values carefully before applying broad cleanup rules: two strings that look similar could represent different identifiers.
Check the result before relying on it
Before using the output for a report or an update, verify a few results against the source lists. Confirm that a match is based on the correct key, that blanks and repeated values were handled as intended, and that the returned list is the right size for the task. A distinct list is not a substitute for a row-level audit when duplicate occurrences carry meaning.
Keep the result separate from the source data while you review it. If you need a fixed snapshot rather than a formula that recalculates when the lists change, copy the spilled results and use Paste Special > Values in a separate destination. This preserves the displayed values without keeping the formula. Retain the original lists so that you can trace how the result was created.
Frequently Asked Questions
How do I match two columns in Excel and show each result once?
In Microsoft 365 or Excel 2021 and later, use UNIQUE around FILTER with XMATCH. The opening formula in this guide returns nonblank values from the first column that also occur in the second, once each.
Does UNIQUE remove duplicates from both lists before matching?
No. In the combined formula, FILTER first selects values from the first list that exist in the second, then UNIQUE removes repeats from that filtered output. The original lists are not changed.
How can I keep duplicate matching rows?
Use the FILTER and XMATCH expression without UNIQUE. It returns every qualifying source row, including repeated values, which is often necessary for transaction or record-level work.
Can Excel match values that are only partly the same?
The formulas here use exact matching, so a whole cell must match. Partial text matching is a different task and should be set up deliberately; otherwise, similar names or codes can create false matches.
Why does my match formula return an error?
Common causes include an unsupported function in an older Excel release, a blocked spill range, a formula entered inside a Table, or a regional setting that uses semicolons rather than commas. Check those issues first.
Related
- How to Unmerge Excel Files in Microsoft Excel
- Unmerge Cells in Excel: Keyboard Shortcuts and Steps
- How to Unmerge Cells in Excel and Keep Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sigma Code in Excel Solver: Build the Formula and SolveIf by “sigma code” you mean the Greek capital sigma, Σ, used to show a sum, Excel Solver…
- PMT Function Negative in Excel? The Sign Rule ExplainedYour PMT function is usually negative because Excel treats the loan amount and its repayments as cash flows…
- Excel Sheet Comparison Formula: Find DifferencesTo compare the same cell on two Excel sheets, enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different") on a third sheet, then fill the…
- How to Add Hours in Excel: Formulas and Time FormatsTo add hours in Excel, enter each duration in a cell, total the cells with SUM, then format…
- Excel Formula for Average Days Between DatesIn Excel for Microsoft 365, if each row has a start date in column A and its matching…
- ABS Function in Excel: Examples and Practical UsesExcel’s ABS function returns the absolute value of a number: its distance from zero, without a negative sign.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.