How to Compare Lists in Excel: A Step-by-Step Guide
Comparing lists in Excel is one of those tasks that sounds simple until you are actually doing it with real data. Two lists that are different lengths, values that almost match but have a trailing space, items that appear in both lists but in different orders. The edge cases add up fast. Excel has five solid methods for list comparison, each one suited to a different situation. The right method depends on whether you need a visual result or a formula result, whether the lists are the same length, and whether you need to pull related data or just confirm presence or absence.
This guide covers all five methods with exact steps and formulas, explains when to use each one, and includes pro tips that solve the most common problems people run into. Watch the linked videos for a visual walkthrough of each technique.
At the end, a free Excel list comparer shows the matches and differences between any two lists instantly and copies them to Excel with live formulas.
Which Excel List Comparison Method Should You Use?
| Method | Best for | Returns |
|---|---|---|
| Conditional Formatting | Quick visual scan, no formula needed | Color highlight |
| IF function | Row-by-row match when lists are the same length and in the same order | Match / No Match text |
| VLOOKUP | Checking presence and pulling related data from the second list | Value or error, or Yes / No |
| COUNTIF | Counting occurrences and finding duplicates, lists in any order or length | Number (0 = not found) |
| Power Query | Large data sets, recurring comparisons, advanced joins | New table in Excel |
Method 1: Compare Lists in Excel With Conditional Formatting
Watch the video walkthrough, or follow the steps below.
Conditional Formatting is the fastest way to compare lists visually. It does not require any formulas. You set a rule and Excel highlights the cells that match your criteria. This is the right method when you need to spot differences quickly and do not need a permanent formula record of which items matched.
How to Highlight Duplicates Across Two Lists
| Step | What to do |
|---|---|
| 1 | Select the range of your first list (for example, A2:A50). |
| 2 | Hold Ctrl and select the range of your second list (for example, B2:B50). |
| 3 | Go to Home, then Conditional Formatting, then Highlight Cells Rules, then Duplicate Values. |
| 4 | In the dialog, choose Duplicate to highlight items that appear in both lists, or Unique to highlight items that appear in only one list. |
| 5 | Select a highlight color and click OK. |
Excel immediately highlights every cell that meets your criteria across both selected ranges. Items highlighted in both columns appear in both lists. Items highlighted in only one column are unique to that list.
How to Use a Custom Conditional Formatting Rule to Find Missing Items
For more control, such as highlighting items in List A that do not appear anywhere in List B, use a custom rule with a formula.
| Step | What to do |
|---|---|
| 1 | Select your first list (for example, A2:A50). |
| 2 | Go to Home, then Conditional Formatting, then New Rule, then Use a formula to determine which cells to format. |
| 3 | Enter =COUNTIF($B$2:$B$50,A2)=0 |
| 4 | Set your highlight color and click OK. |
Every cell in Column A whose value does not appear anywhere in Column B will be highlighted. Change the =0 to >0 to highlight matches instead of non-matches.
Pro tip: Clean both lists before comparing. A space character that is invisible to the eye makes Excel treat "Smith" and "Smith " as different values, producing false non-matches. Add a helper column with =TRIM(A2), fill it down, then copy it and paste as values over the original list.
Method 2: Compare Lists in Excel With the IF Function
Watch the video walkthrough, or follow the steps below.
The IF function creates a helper column that checks each row individually and returns a text result. This works well when your lists are the same length and aligned in the same order: List A in Column A and List B in Column B, with corresponding items on the same row.
It is the wrong tool when lists are different lengths or in different order, because it compares A2 to B2, A3 to B3, and so on, row by row, not value by value across the full list.
| Step | What to do |
|---|---|
| 1 | Put your first list in Column A and your second list in Column B, with headers in Row 1. |
| 2 | In C2, enter =IF(A2=B2,"Match","No Match") |
| 3 | Press Enter, then drag the fill handle (the small square at the bottom-right corner of C2) down to apply the formula to all rows. |
| 4 | Column C now shows Match or No Match for every row. |
If you need a case-sensitive comparison, where "Apple" and "apple" should be treated as different, replace the formula with: =IF(EXACT(A2,B2),"Match","No Match")
Pro tip: For more nuanced results, use nested IF or combine with ISNUMBER and MATCH: =IF(ISNUMBER(MATCH(A2,$B$2:$B$50,0)),"Found","Not Found"). This checks for the value of A2 anywhere in the full Column B range, not just the same row.
Method 3: Compare Lists in Excel With VLOOKUP
Watch the video walkthrough, or follow the steps below.
VLOOKUP is the right choice when you need to check whether items in one list exist in another list and also pull back a related value from that second list: a price, a category, a status, a date. It searches the entire second list range for each value in the first list, so it works when lists are different lengths or in different order.
When an item is not found, VLOOKUP returns an error by default, which is why the formula below wraps it in ISNA to return a clean Yes/No instead.
How to Check Whether Each Item Exists (Yes or No)
| Step | What to do |
|---|---|
| 1 | In a helper column next to List A, enter =IF(ISNA(VLOOKUP(A2,$B$2:$B$50,1,FALSE)),"No","Yes") |
| 2 | Drag the formula down to cover all rows in List A. |
| 3 | Yes means the value in Column A was found somewhere in Column B. No means it was not. |
How to Return a Related Value From the Second List
If Column B contains names and Column C contains the related department, and you want to bring the department into your first list:
=VLOOKUP(A2,$B$2:$C$50,2,FALSE)
The 2 tells Excel to return the value from the second column of the lookup range (Column C). Change it to 3 for the third column, and so on. The FALSE at the end requires an exact match, so always use FALSE for list comparisons.
Pro tip: VLOOKUP requires the lookup column to be the leftmost column of your lookup range. If it is not, use INDEX/MATCH instead: =INDEX($C$2:$C$50,MATCH(A2,$B$2:$B$50,0)). This is more flexible, handles any column order, and does not break when columns are inserted.
Method 4: Compare Lists in Excel With COUNTIF
Watch the video walkthrough, or follow the steps below.
COUNTIF is the most flexible formula for list comparison. It counts how many times each value in List A appears in List B, which means it handles lists of different lengths, items in any order, and even items that appear multiple times in the second list. A result of 0 means the item is unique to List A.
A result of 1 or more means it appears in List B, and the exact number tells you how many times.
| Step | What to do |
|---|---|
| 1 | In a helper column next to List A, enter =COUNTIF($B$2:$B$50,A2) |
| 2 | Drag the formula down to cover all rows in List A. |
| 3 | Filter or sort the helper column by value: 0 means not in List B, and 1 or more means found in List B. |
To convert the count to a text label: =IF(COUNTIF($B$2:$B$50,A2)>0,"In Both Lists","List A Only")
Pro tip: Run COUNTIF in both directions. Add a second helper column next to List B with =COUNTIF($A$2:$A$50,B2) to see which items in List B do not appear in List A. Together, the two helper columns give you a complete picture of overlap and differences without needing to sort or restructure either list.
Method 5: Compare Lists in Excel With Power Query
Power Query is the right tool for large data sets with thousands of rows, recurring comparisons you need to refresh regularly, and comparisons that require joins, such as finding items in List A that are not in List B, items in both, or everything from both lists. It does not require formulas and produces a clean output table that can be refreshed with one click when the source data updates.
| Step | What to do |
|---|---|
| 1 | Click anywhere in your first list. Go to Data, then From Table/Range. If prompted, confirm the table range and whether your list has headers, then click OK. The Power Query Editor opens. |
| 2 | Click Close & Load To and choose Only Create Connection. This loads List A into Power Query without adding a new sheet. |
| 3 | Repeat steps 1 and 2 for your second list. |
| 4 | In Power Query, go to Home, then Merge Queries, then Merge Queries as New. |
| 5 | Select your first query in the top drop-down and your second query in the bottom drop-down, click the column to match on in each table, and choose a join type (see the table below). |
| 6 | Click OK, expand the merged column if needed, then click Close & Load to send the results to a new sheet. |
| Join type | Returns |
|---|---|
| Left Anti | Items in List A that are not in List B |
| Inner | Items that appear in both lists |
| Full Outer | All items from both lists, with nulls where there is no match |
Pro tip: Once the Power Query merge is set up, refreshing it takes one click: right-click the output table and select Refresh, or go to Data > Refresh All. This makes Power Query far superior to formula-based comparison for recurring reports where the lists update regularly.
Free Excel List Comparer
Paste any two lists into the comparer below to see the result before you build a single formula. It sorts every item into three columns: in both lists, only in List A, and only in List B.
The example shows the two problems this guide keeps warning about. "Lee " in List A has a trailing space, and "KIM" in List B is capitalized. With both clean-up options on, they match. Turn them off and watch them fall into the "only in" columns, which is exactly what happens in Excel when data is not cleaned first.
Copy the comparison to Excel and paste it in cell A1. Both lists come across with a live formula next to every item that checks the other list, trimming spaces as it goes, plus counts for in both, only in A, and only in B. Print it to share the differences.
Excel List Comparer
Paste List A and List B, one item per line. Choose whether to ignore extra spaces and capitalization, the two most common reasons Excel says items do not match. The comparer shows which items are in both lists, only in A, and only in B. Copy it to Excel to get both lists with live COUNTIF formulas in each direction. Nothing you enter is saved or sent anywhere.
Common Excel List Comparison Problems and How to Fix Them
Values Look the Same but Are Not Matching
The most common cause is invisible characters: leading spaces, trailing spaces, or non-breaking spaces that paste in from other systems. Use =TRIM(A2) to remove leading and trailing spaces from a value before comparing. For non-breaking spaces that TRIM does not catch, use =SUBSTITUTE(TRIM(A2),CHAR(160),"").
Case Sensitivity Is Causing Wrong Results
COUNTIF, VLOOKUP, and Conditional Formatting are all case-insensitive by default, so "apple" and "APPLE" will be treated as a match. If you need case-sensitive comparison, use =EXACT(A2,B2) for row-by-row checks or a SUMPRODUCT array formula for cross-list comparison: =SUMPRODUCT((EXACT($B$2:$B$50,A2))*1)>0 returns TRUE only for an exact case match.
VLOOKUP Returns #N/A for Everything
Check that the lookup column is the leftmost column of the range in your VLOOKUP formula. If it is not, switch to INDEX/MATCH. Also check for data type mismatches. If Column A contains numbers stored as text and Column B contains true numbers, Excel will not match them. Use =VALUE(A2) or =TEXT(B2,"0") to standardize the data type before comparing.
Want to Go Deeper on Excel?
List comparison is one building block in a larger Excel toolkit. If you want to master the functions that make data analysis fast and reliable, the Excel training courses at XecuteTheVision take you from foundational concepts through advanced techniques used in real business environments.
Opens in Google Drive. View and download for free.
Related Reading
70 Essential Business Math Formulas Made Easy →
Frequently Asked Questions About Comparing Lists in Excel
How do I compare two lists in Excel to find differences?
The fastest way is Conditional Formatting. Select both lists, go to Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values, and choose Unique to highlight items that appear in only one list. For a formula approach, use =COUNTIF($B$2:$B$100,A2)=0 in a helper column. A result of TRUE means the item in Column A does not appear in Column B.
What is the best formula to compare two lists in Excel?
For a simple match or no-match result, =IF(COUNTIF($B$2:$B$100,A2)>0,"Match","No Match") is the most flexible option because it works even when lists are different lengths and items are not in the same order. VLOOKUP works well when you also need to return a value from the second list. EXACT() is the right choice when you need case-sensitive comparison.
How do I compare two lists in Excel for duplicates?
Use COUNTIF to count how many times each item in one list appears in the other. =COUNTIF($B$2:$B$100,A2) in a helper column returns 0 for items unique to List A and 1 or more for items that appear in both lists. Pair this with Conditional Formatting, highlighting any cell where the COUNTIF result is greater than 0, to get a visual duplicate map across both lists.
Can I compare lists in Excel if they are different lengths?
Yes. COUNTIF and VLOOKUP both work regardless of whether the lists are the same length. Both formulas reference the full range of the second list as an absolute reference (for example, $B$2:$B$100), so every item in the first list is checked against the complete second list. Power Query is the best option for very large lists where formulas become slow.
How do I compare two lists in Excel and pull matching data?
Use VLOOKUP or INDEX/MATCH. =VLOOKUP(A2,$B$2:$C$100,2,FALSE) finds A2 in Column B and returns the corresponding value from Column C. =INDEX($C$2:$C$100,MATCH(A2,$B$2:$B$100,0)) does the same but works with the lookup column anywhere in the range and keeps working when columns are inserted.
Why do two values that look the same not match in Excel?
Usually there is an extra space, a non-breaking space, or a number stored as text. Clean the data with TRIM, SUBSTITUTE, or VALUE before comparing.