Yes. To check whether each entry in one Excel list appears in another, put the first list in A2:A6 and the comparison list in D2:D6, then enter this in B2 and fill down: =IF(COUNTIF($D$2:$D$6,A2)>0,"Match","No match"). It labels each value in column A as a match or no match; the sections below explain how to handle duplicates, return related information, and fix entries that look identical but do not match.
How the basic match formula works
COUNTIF checks a range for cells that meet a criterion. Here, the fixed range $D$2:$D$6 is the list to search, and A2 is the value being sought. The dollar signs keep the search range in place when you copy the formula down, while the relative reference A2 changes to A3, A4, and so on.
For example, suppose A2:A6 contains Cedar, Maple, Birch, Pine, and Cedar, while D2:D6 contains Birch, Cedar, and Oak. After filling the formula down, the results are Match, No match, Match, No match, and Match. Cedar appears twice in the first list, but both rows match because Cedar appears at least once in the comparison list.
This method answers a simple yes-or-no question: does a value appear in the other range? It does not count how many times it appears, identify which row matched, or distinguish uppercase from lowercase. Those tasks call for different approaches.
Compare two lists step by step
- Put the values you want to check in one column and the reference values in another. Add headers if they help explain the data.
- Select the first result cell beside the first value, such as B2 when the list starts in A2.
- Enter the COUNTIF formula, adjusting the range to cover the comparison list. Keep dollar signs around that range so it stays fixed when copied.
- Press Enter. To apply the formula to more rows, select the cell and drag its fill handle down, or double-click the fill handle when the neighboring data column is continuous.
- Filter the result column for Match or No match if you want to review only one group. In Excel, use Data > Filter to add filters to a header row.
If the lists are longer or likely to change, you can use whole-column references, such as D:D, but a defined range or an Excel Table is often easier to audit. A table expands as rows are added, helping keep the comparison range current. Avoid including a header in the data range unless you intend to search it too.
What Excel considers a match
COUNTIF treats text matching as case-insensitive, so Cedar and CEDAR count as the same entry. It also treats a number stored as a number differently from a number stored as text in some comparisons. For instance, a numeric 104 in one list and text “104” in another can produce a surprising result, especially when data was imported from a website or another system.
COUNTIF criteria also recognize the wildcard characters * and ?. An asterisk can stand for any number of characters, and a question mark can stand for one character. That is useful when you deliberately want flexible matching, but it can cause unexpected matches if a searched entry itself contains one of those characters. For ordinary lists of names or IDs, inspect unusual results rather than assuming the formula has made a character-for-character comparison.
Exact matching means different things in different tasks. The basic formula checks whether Excel considers the value equal under COUNTIF’s rules. If capitalization matters, if punctuation matters, or if an ID must match every character exactly, use a method designed for that requirement and test it on representative records.
Return information from the matching row
A yes-or-no label may not be enough. If the comparison list has a related value, such as a department or price, XLOOKUP can find the entry and return information from the same row. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not have it.
Assume the values to find are in A2:A6, the lookup list is in D2:D6, and the corresponding department names are in E2:E6. Enter this in B2 and fill down: =XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6,"Not found",0). If A2 is Cedar, D3 is Cedar, and E3 is North, the result is North. The final argument 0 requests an exact match, and “Not found” is returned when there is no match.
XLOOKUP returns a value from a matching row; it does not show every occurrence when the lookup list contains duplicates. It returns the first match it finds in the search range. If duplicate records have different associated details, decide which record should count before using the returned result in a report.
Find duplicates and count occurrences
“Does this entry exist in the other list?” and “Is this entry duplicated?” are related but different questions. To find repeated values within a single column, count occurrences in that same column. With entries in A2:A100, put this in B2 and fill down: =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"). If Cedar occurs in A2 and A8, both rows are labeled Duplicate.
To count how many times an entry occurs in the comparison list, use COUNTIF directly. With the value to count in A2 and comparison entries in D2:D100, enter =COUNTIF($D$2:$D$100,A2) in B2. If Cedar appears three times in D2:D100, the formula returns 3. A zero means it does not appear in that range.
Duplicates can be meaningful. A list of product IDs might be expected to contain one row per product, while a transaction list may correctly contain the same customer or product many times. Decide whether duplicates are errors or valid repeated records before removing them. Excel’s Data > Remove Duplicates command changes the selected data, so make a copy first if the original records need to be preserved.
Fix spaces and imported text
Two entries can look alike on screen while containing different characters. A trailing space after a name, a nonbreaking space copied from a web page, or a nonprinting character from an export can prevent a match. Before changing the comparison formula, check whether the source values need cleaning.
With the original text in A2, enter this in a helper column and copy it down: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). For an illustrative value containing ordinary leading and trailing spaces plus a nonbreaking space between words, the result removes those extra spaces and replaces the nonbreaking space with a regular space. CLEAN removes certain nonprinting characters, SUBSTITUTE replaces character 160, and TRIM removes extra ordinary spaces while leaving single spaces between words.
Apply the same cleaning approach to both lists, then compare the cleaned helper columns. Keep the original columns until you have checked the results; replacing source data can hide useful information about how it arrived. TRIM and CLEAN do not solve every possible encoding or punctuation issue, so inspect a few mismatches manually if the data comes from several systems.
Also check whether numbers have been imported as text. In a spare cell, Excel’s warning indicator may offer a Convert to Number option; the option depends on the cell contents and settings. You can also use the Data tab’s available text conversion tools, but verify the result before applying a conversion to IDs. Leading zeros may be meaningful in codes such as 0042, even though Excel treats ordinary numbers differently.
Highlight matching entries visually
For a quick visual review, place the yes-or-no formula beside the list and filter for Match. This is often clearer than coloring values because the label remains explicit and can be included in a report. If you use conditional formatting, select the cells you want to emphasize and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to find repeats within the selected range.
That Duplicate Values rule is not a general comparison of two separate columns: it looks for duplicate values in the range selected for the rule. To highlight values from one list that occur in another, use a formula-based conditional formatting rule and set its references carefully. Select the target cells first, choose Home > Conditional Formatting > New Rule, then choose “Use a formula to determine which cells to format.” A helper column with COUNTIF is usually easier to understand and troubleshoot, particularly for someone else who may inherit the workbook.
Choose the right method for the job
- Use COUNTIF when you need a clear Match or No match result for every item in one list.
- Use XLOOKUP when you need a value from the row where the match was found, and your Excel version supports it.
- Count occurrences when duplicates or the number of records matter, rather than treating every result as a simple yes or no.
- Clean both lists when imported spaces or characters may be responsible for unexpected mismatches.
- Use a case-sensitive comparison only when capitalization is genuinely meaningful, such as for a specific code system.
For larger recurring comparisons, convert each dataset to an Excel Table using Insert > Table. Tables make headers and data boundaries easier to recognize, and their ranges expand as records are added. If the workbook is part of a regular import process, keeping the raw data, cleaned columns, and match results separate makes it easier to trace an incorrect result back to its source.
Frequently Asked Questions
How do I compare two columns in Excel and show matches?
Put a COUNTIF formula beside the first list, with the second list as the search range. For example, if values are in A2:A20 and D2:D20, use the same pattern as the opening formula, change the range to $D$2:$D$20, and fill down.
Can Excel highlight values that appear in both lists?
Yes. A helper column using COUNTIF can label matches, after which you can filter or apply formatting. The built-in Duplicate Values rule checks for repeats within the selected range, so it is not by itself a comparison between two separate lists.
Why does Excel say two identical-looking values do not match?
Common causes include leading or trailing spaces, nonbreaking spaces, nonprinting characters, and numbers stored as text in one list but as numbers in the other. Check the underlying values, clean both lists consistently, and preserve the originals while investigating.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF’s text criteria are not case-sensitive, so Cedar and CEDAR match. If case must be significant, use a case-sensitive comparison method rather than relying on COUNTIF.
How do I find values in one list that are missing from another?
Use the same formula as for matching, then filter the result column for No match. That shows entries in the checked list that were not found in the reference list; reverse the two ranges to find omissions in the other direction.
Will the formula update when I add more rows?
Not if the fixed range ends above the new rows. Extend the range, use a sufficiently large range, or put the source data in an Excel Table and use a table-based reference. Check the formula range whenever the source list grows.
Related
- Python Pandas Excel to CSV: Steps, Options, and Fixes
- Python Pandas Excel to Dictionary: Practical Guide
- Python pandas Excel Merge: Join Workbooks and Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LET Function: Make Advanced Formulas ClearerYes—Excel lets you build advanced formulas, and the LET function makes them easier to read by giving names…
- DATEDIF Excel Function: Examples and Common IssuesExcel’s DATEDIF function calculates the time between two dates in years, months, or days. For example, with a…
- Excel Formula to Remove Duplicates From One ColumnIn Excel for Microsoft 365 or Excel 2021, enter =UNIQUE(A2:A100) in an empty cell to return a live…
- How to Filter Rows by Color in Excel: A Practical GuideTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- VLOOKUP to Compare Two Columns on Different SheetsYes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s…
- Excel Text Functions List: Names, Uses, and ExamplesExcel’s text functions help you extract, combine, clean, compare, format, and convert text or values. The main functions…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.