To find whether names in column A also appear in column B, enter =IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Not found") in C2, assuming the lists are in A2:A100 and B2:B100, then fill the formula down. Excel reports “Found” for each name in A that appears anywhere in B; use conditional formatting or a filtered match list if you want a visual result or a separate list of shared names.
What the comparison checks
The formula compares each cell in the first list against the entire second list. For example, if A2 contains “Jordan Lee” and that exact text is somewhere in B2:B100, C2 returns “Found.” If it is absent, the result is “Not found.” The dollar signs keep the B range fixed as you copy the formula down, while the reference to A2 changes to A3, A4, and so on.
This is a one-way check: it asks whether each name in column A is in column B. If you need to identify names in B that are missing from A as well, make a second check in the other direction. This matters when the lists are different sizes or one list contains extra entries. A match also means a value occurs at least once; it does not count how many times the name appears in either list.
Excel’s COUNTIF comparison is not case-sensitive, so “Jordan Lee” and “JORDAN LEE” count as a match. It compares cell contents, not a person’s identity: two people with the same name will appear to match, while the same person listed under a different spelling will not. If identity matters, compare a unique identifier such as an employee number instead of relying on names alone.
Use a formula to check every row
For a clear, reusable result beside each entry, follow these steps in Excel for Microsoft 365 on Windows:
- Put the first set of names in A2:A100 and the second set in B2:B100. Keep the headings in row 1, outside the ranges.
- In C1, add a heading such as “In column B?”
- With names in A2:A100 and B2:B100, enter
=IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Not found")in C2. - Press Enter, select C2, and copy the formula down through the rows containing names in column A. You can drag the fill handle or copy and paste it into the remaining cells.
- Filter column C for “Found” if you want to see only the entries from A that also occur in B.
The formula does not need the lists to have the same number of rows. Change the range endpoints to cover the full lists, but avoid including thousands of unnecessary blank rows if the workbook is large. For a quick check of one name only, put the name in a cell such as D2 and use that cell as the criterion instead of A2.
For a list that may grow, you can convert each list into an Excel table using Insert > Table and use table column references in formulas. Tables expand when rows are added, reducing the chance that a new name falls outside a fixed range. If you use ordinary ranges, remember to extend the formula’s lookup range when you add entries below row 100.
Highlight shared names without adding a result column
Conditional formatting colors matching cells in place. This is useful for a quick visual review, although it does not create a separate list or explain which counterpart matched. Set up a rule for each column so the comparison runs in both directions:
- Select A2:A100. Choose Home > Conditional Formatting > New Rule, then choose “Use a formula to determine which cells to format.”
- With the names in A2:A100 and B2:B100, use
=COUNTIF($B$2:$B$100,A2)>0as the rule formula. Choose a fill color and confirm. - Select B2:B100 and create a second rule using
=COUNTIF($A$2:$A$100,B2)>0. Choose a format and confirm.
Each rule begins with the top-left cell of the selected range: A2 for the first rule and B2 for the second. The lookup range is fixed, while the cell being checked adjusts for each row. If the color appears on blank cells, check that the selected range excludes the header and that your rule is applied to the intended cells. In Excel for Mac, the Conditional Formatting controls are also on the Home tab; the dialog layout can differ slightly from Windows.
Return a separate list of matches
If you want a compact output instead of “Found” beside every original name, Excel for Microsoft 365 can filter the first list to show only names that appear in the second. The result spills into the cells below the formula, so leave that space clear. UNIQUE removes repeated names from the output, and the final argument supplies a message if there are no matches.
With names in A2:A100 and B2:B100, enter =UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(B2:B100,A2:A100)>0),"No matches")) in an empty cell. It returns each nonblank name from A that occurs in B once, preserving the order in which names first appear in A. FILTER, UNIQUE, and dynamic array formulas are available in Microsoft 365 and Excel 2021 or later; older Excel versions can use the row-by-row formula and filter its results instead.
This output follows the first list. To produce a list based on names in B, switch the source and lookup ranges. To retain repeated entries rather than return distinct names, omit UNIQUE and use FILTER alone: with the same layout, enter =FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(B2:B100,A2:A100)>0),"No matches") in a clear cell. A repeated name in A then appears repeatedly if each occurrence meets the condition.
Choose the right matching method
The best method depends on what you need to do with the result:
- COUNTIF in a helper column: Best for checking many rows, filtering the results, and retaining a visible status beside each original name. It works in older Excel versions as well as Microsoft 365.
- Conditional formatting: Best for a quick visual scan of the original lists. It is easy to overlook results in a long range, and the highlight alone is not a reusable match report.
- FILTER with UNIQUE: Best for creating a clean, automatically updating list of shared names in a modern Excel version. Make sure the cells where the result will spill are empty.
If you only need to check one value and use Microsoft 365 or Excel 2021, XMATCH is another option. With the name to check in A2 and the lookup list in B2:B100, use =IF(ISNUMBER(XMATCH(A2,$B$2:$B$100,0)),"Found","Not found"). The final zero requests an exact match. XMATCH can be helpful when you later need a match position, but COUNTIF is usually easier when the question is simply whether a name exists.
Handle capitalization, spaces, and spelling differences
COUNTIF ignores capitalization, but it does not automatically standardize spelling, punctuation, or ordinary extra characters. “Ana Maria” and “Ana Maria” (with two spaces) may not match as expected, nor will “Jon Smith” and “John Smith.” A match result should therefore be treated as a text comparison, not proof that two records refer to the same person.
Leading or trailing spaces are a common source of apparent mismatches, especially when data has been pasted from a website or exported from another system. One approach is to create cleaned helper columns and compare those columns instead. With a name in A2, use =TRIM(CLEAN(A2)) in a helper cell and fill down; do the same for column B. TRIM removes extra ordinary spaces at the ends and reduces repeated internal spaces, while CLEAN removes certain nonprinting characters. Then point the match formula at the cleaned lists.
TRIM and CLEAN do not remove every character that may look like a space. Nonbreaking spaces can appear in copied text. With a name in A2, a more targeted cleanup formula is =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). This replaces character 160 with a regular space before cleaning and trimming. Check the cleaned values before using them for important records: standardizing text can change how it displays, and unusual characters or accents may need a deliberate data-cleaning rule.
COUNTIF also treats the asterisk (*) and question mark (?) as wildcard characters in criteria. That can affect a literal name or label containing one of those symbols. If such characters are possible and you need a strict text match, use the exact-match XMATCH method described above rather than assuming COUNTIF treats every character literally.
Make the comparison case-sensitive
Names are normally compared without regard to capitalization, but some identifiers distinguish uppercase from lowercase. EXACT performs a case-sensitive comparison. With the value to check in A2 and the comparison list in B2:B100, use =IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$100))>0,"Found","Not found") in C2 and fill it down. For example, “Lee” and “LEE” will not count as the same text under this test. Use case sensitivity only if the source data’s rules make capitalization meaningful; it is usually not a reliable way to distinguish people with ordinary names.
Common mistakes and useful checks
- Reversed ranges: COUNTIF takes the range to search first and the value to look for second. For the A-to-B check, search B2:B100 for the value in A2.
- Unanchored lookup range: If the range lacks dollar signs, it moves as you fill down and can gradually exclude earlier rows. Use
$B$2:$B$100for a fixed lookup list. - Headers included: A header such as “Name” can be returned as a match if it appears in both columns. Start the ranges at the first data row.
- Unexpected duplicates: COUNTIF returns a match if the name appears at least once; it does not reveal whether it occurs once or many times. Use a count when frequency matters.
- Numbers stored as text: Names are usually text, but imported identifiers may be stored inconsistently as numbers and text. Standardize the data type if the compared values are IDs rather than names.
For an unexpected result, inspect the two cells directly, check for spaces, and test the formula on a small known example. Also verify that the selected ranges cover all rows. Sorting one column without sorting its associated records can damage the meaning of a row-by-row comparison, though the COUNTIF method itself searches the whole specified range and does not require the lists to be sorted.
Frequently Asked Questions
How do I find names that appear in both columns?
Use a helper formula such as =IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Not found") beside the first list and filter for “Found.” For a separate distinct list in Microsoft 365 or Excel 2021, use FILTER with UNIQUE.
How can I find names in column A that are missing from column B?
Use the same COUNTIF formula, but return “Missing” when the count is zero: with names in A2:A100 and B2:B100, enter =IF(COUNTIF($B$2:$B$100,A2)=0,"Missing","Found") in C2 and fill down. This still checks A against B only.
Does Excel treat uppercase and lowercase names as different?
COUNTIF does not distinguish case, so “MORGAN” and “Morgan” match. For a case-sensitive check, use EXACT inside SUMPRODUCT as shown above.
Can I compare two columns when they have different numbers of rows?
Yes. The lists do not need equal lengths. Set the lookup range to cover the full second list and fill the result formula down for the rows in the first list.
Why does a name that looks identical not match?
Hidden spaces, nonprinting characters, punctuation, or spelling differences may make the cell contents different. Compare the underlying text and, if appropriate, clean both lists consistently before running the match.
Related
- Excel Vendor List Template: Build and Manage Your Records
- Excel Data Validation from a List: Setup and Troubleshooting
- Excel Named Ranges: How to Create and Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Making a Template in Word: Steps, Styles, and SetupTo make a template in Microsoft Word for Microsoft 365 on Windows, create or open a document with…
- Excel Not Responding: How to Save Your Work SafelyIf Excel says Not Responding, first stop clicking and give it time to finish; if the window still…
- Power Query Absolute Value: How to Use Number.AbsIn Power Query, use the M function Number.Abs to return a number’s absolute value: for example, Number.Abs(-25) returns…
- Archive All Emails in Outlook: Bulk Methods and LimitsTo archive a large number of Outlook emails, select messages in a folder or search results and choose…
- How Replace Works in Excel: Find and ReplaceIn Excel, “replace” can mean the REPLACE function, which swaps characters at a specified position in text, or…
- Inserting Citations in Word: A Step-by-Step GuideTo insert a citation in Microsoft Word for Microsoft 365 on Windows, place the cursor where the citation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.