How to Compare Two Lists in Excel: 8 Methods + Formulas (2026)

You need to compare two columns in a spreadsheet — a master list and an updated list — and know exactly which items match, which are missing, and which are new. Whether you are reconciling customer IDs, cross-referencing product SKUs, or auditing supplier invoices, knowing how to compare two lists in Excel is one of the most practical skills you can have. This guide covers all eight methods, from zero-formula visual tools to advanced Power Query merges, so you can pick the right approach for your situation. If you also need to compare general lists outside of Excel, we have a separate guide for that.

Why Comparing Two Lists in Excel Gets Messy

On the surface, comparing two lists sounds trivial. In practice, four things consistently cause formulas to silently fail or produce misleading results:

• Invisible whitespace. A value imported from a CSV or copied from a web page often carries a leading or trailing space. "Apple" and "Apple " look identical in a cell but will never match in VLOOKUP or COUNTIF. Run =TRIM(A2) on both lists before comparing.
• Mixed data types. A number stored as text (common after CSV imports) will not match the same number stored as a real number. Excel shows them identically but treats them differently internally. Check the cell alignment — left-aligned numbers are usually text.
• Formula errors propagating silently. A single #N/A in a helper column can cascade through dependent cells. Always wrap lookup formulas in IFERROR or use XLOOKUP's built-in not-found argument.
• Performance on large datasets. Volatile formulas like COUNTIF recalculate on every worksheet change. On 100,000-row lists, this can make your workbook unusable. Power Query or a one-time paste-as-values approach works better at that scale.

With those pitfalls in mind, here are the eight methods ranked from simplest to most powerful.

Method 1 — Conditional Formatting (No Formulas Needed)

Conditional Formatting is the fastest visual method and requires zero formula knowledge. If you want to compare lists in Excel for duplicates without writing a single formula, this is the method to start with — Excel does the matching for you and highlights results directly in the cells.

Option A — Highlight Duplicate Values

This option highlights values that appear in both lists (duplicates across columns).

1. Select both columns — for example, A2:A100 and B2:B100 together. Hold Ctrl to select non-adjacent ranges.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. In the dialog, choose Duplicate and a highlight color, then click OK.
4. Every value that appears in both columns is now colored. Values highlighted in only one column are unique to that list.

Limitation: This method highlights the value wherever it appears, not which list it belongs to. It also does not tell you how many times a value appears — just that it exists in both.

Option B — Custom Rule with COUNTIF (Highlight Unique Values Only)

To highlight values in List A that are missing from List B, use a custom formula rule:

1. Select List A only (e.g., A2:A100).
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter the formula:

   =COUNTIF($B$2:$B$100,A2)=0

4. Set a red fill and click OK. Every highlighted cell in List A is missing from List B.
5. Repeat for List B to find values missing from List A.

Best for: Quick visual audits, presentations, or any time you need results without a helper column cluttering the sheet. Works in all Excel versions including Excel for Mac.

Method 2 — Row-by-Row Match with the Equal Sign (=A2=B2)

The simplest formula possible. If your two lists are in adjacent columns and the rows line up (same item in row 2 of both lists), a direct comparison takes one formula:

=A2=B2

This returns TRUE if the values match and FALSE if they differ. For a friendlier output, wrap it in an IF statement:

=IF(A2=B2,"Match","Difference")

Drag the formula down column C to cover all rows. You now have a clear Match/Difference label for every row.

Critical limitation: This method is entirely order-sensitive. If List A has "Apple" in row 2 and List B has "Apple" in row 5, the formula returns FALSE because it only compares the same row number. Use COUNTIF (Method 3) instead when the lists may be sorted differently or have different lengths.

For a broader discussion of string comparison basics and how text matching works under the hood, see our dedicated guide.

Method 3 — COUNTIF for Presence/Absence Across Lists

COUNTIF is the workhorse formula for excel list comparison. It checks whether a value from one list exists anywhere in the other list — regardless of row order.

Basic syntax

=COUNTIF($B$2:$B$100,A2)

This counts how many times the value in A2 appears in the range B2:B100. A result of 0 means A2 is not present in List B. A result of 1 or more means it is.

Converting to a Match/No Match label

Wrap in IF for a readable output:

=IF(COUNTIF($B$2:$B$100,A2)>0,"Match","No Match")

Or return a boolean directly:

=COUNTIF($B$2:$B$100,A2)>0

This returns TRUE if A2 exists in List B, FALSE if it does not.

Finding items in List B that are missing from List A

Add a second helper column next to List B:

=IF(COUNTIF($A$2:$A$100,B2)=0,"Missing from A","Present")

Key points: Use dollar signs ($) on the lookup range so the reference does not shift when you drag the formula down. COUNTIF is case-insensitive — "APPLE" and "apple" count as the same. Duplicate detection is built in: if a value appears three times in List B, COUNTIF returns 3, which is useful for spotting not just presence but frequency.

Best for: Lists that may be sorted differently or have different lengths. Works in all Excel versions and Excel for Mac.

Method 4 — VLOOKUP to Cross-Reference Two Lists

VLOOKUP is Excel's classic lookup formula and remains the most widely taught method for excel cross reference two lists operations. Rather than just checking presence, it can also retrieve an associated value from List B.

Syntax for exact match

=VLOOKUP(A2,$C$2:$D$100,2,FALSE)

• A2 — the value to look up from List A.
• $C$2:$D$100 — the range containing List B in the first column, plus any extra columns you want to retrieve.
• 2 — return the value from the 2nd column of the lookup range.
• FALSE — exact match only. Never omit this argument when comparing lists.

If A2 does not exist in List B, VLOOKUP returns #N/A. Handle this with IFERROR:

=IFERROR(VLOOKUP(A2,$C$2:$D$100,2,FALSE),"Not found")

Using VLOOKUP purely for presence checking

When you only need to know if a match exists (not retrieve a value), use:

=IFERROR(VLOOKUP(A2,$C$2:$C$100,1,FALSE),"No Match")

If A2 exists in List B, the formula returns the matched value. If not, it returns "No Match".

Known limitation: VLOOKUP can only look to the right within its lookup range. The lookup column must always be the leftmost column of the range argument. For left-direction lookups, use XLOOKUP or INDEX/MATCH. For official documentation, see Microsoft's XLOOKUP reference, which also explains when VLOOKUP is the appropriate legacy choice.

Best for: Retrieving associated data (e.g., price from SKU list). Excel 2007 and above, including Excel for Mac.

Method 5 — XLOOKUP (Modern Excel 365 Replacement)

XLOOKUP replaces VLOOKUP in Excel 365 and Excel 2021. The syntax is cleaner, more flexible, and safer out of the box. For compare two columns tasks, it handles what VLOOKUP cannot.

Syntax

=XLOOKUP(A2,$C$2:$C$100,$D$2:$D$100,"Not found")

• A2 — the lookup value.
• $C$2:$C$100 — the lookup array (List B, single column).
• $D$2:$D$100 — the return array (what to retrieve when a match is found).
• "Not found" — what to return when no match exists. This replaces the need for IFERROR.

Presence check only

=XLOOKUP(A2,$C$2:$C$100,$C$2:$C$100,"No Match")

Here the lookup array and return array are the same column. If A2 exists in List B, the formula returns the matched value. If not, it returns "No Match".

Key advantages over VLOOKUP

• Can look left — the return array does not have to be to the right of the lookup array.
• No column index number to maintain — changing the source table does not break the formula.
• Built-in not-found argument — no outer IFERROR needed.
• Default match mode is exact match — the fourth argument defaults to 0 (exact), unlike VLOOKUP's confusing TRUE default.

Availability: Excel 365 (Windows and Mac), Excel 2021, Excel Online. Not available in Excel 2019 or earlier.

Method 6 — INDEX/MATCH for Two-Way Cross-Reference

INDEX/MATCH was the power-user alternative to VLOOKUP before XLOOKUP arrived. It remains relevant in Excel 2019, Excel 2016, and Google Sheets, and it is still the go-to for horizontal lookups (looking across rows rather than down columns).

Standard vertical lookup

=INDEX($D$2:$D$100,MATCH(A2,$C$2:$C$100,0))

• MATCH(A2,$C$2:$C$100,0) — finds the row number of A2 within List B. The 0 argument means exact match.
• INDEX($D$2:$D$100, ...) — returns the value from column D at that row number.

Wrap in IFERROR to handle no-match cases:

=IFERROR(INDEX($D$2:$D$100,MATCH(A2,$C$2:$C$100,0)),"Not found")

Checking presence only (no retrieval needed)

=IF(ISNUMBER(MATCH(A2,$C$2:$C$100,0)),"Match","No Match")

MATCH returns a number (row position) when it finds a value, and an error when it does not. ISNUMBER converts those outcomes to TRUE/FALSE cleanly.

Best for: Left-direction lookups in Excel 2019 or earlier, and any scenario where you need to look across rows. In Excel 365, prefer XLOOKUP for simpler syntax. For deeper context on this pattern, Exceljet's INDEX/MATCH guide is an excellent reference.

Method 7 — Go To Special → Row Differences

Almost nobody knows this one exists. Excel has a built-in tool called Go To Special that can instantly select all cells in a row that differ from the corresponding cell in another column — with no formula required.

Step-by-step

1. Put both lists in adjacent columns — List A in column A, List B in column B. The rows must be aligned (same item in the same row).
2. Select both columns together: click A2, hold Shift, then click the last cell of column B.
3. Press F5 (or Ctrl+G on Windows) to open the Go To dialog.
4. Click Special, select Row Differences, and click OK.
5. Excel immediately selects every cell in column B that differs from the corresponding cell in column A.
6. Apply a fill color to make the differences visible: press Alt+H, H and choose a color.

Important note: Go To Special compares each row left-to-right and anchors on the leftmost selected column. Differences are highlighted in the right column relative to the left. This method is strictly row-order-sensitive — like Method 2 — so it works best when both lists are sorted identically.

Best for: Quick one-off audits when both lists are aligned row-for-row and you do not want to add a helper column. Works in all Excel versions.

Method 8 — Power Query Merge for Big, Multi-Sheet Datasets

When your lists are in different workbooks, different sheets, or contain more than 50,000 rows, Power Query (also called Get & Transform) is the right tool. Unlike formulas, Power Query results do not recalculate on every keystroke, and the merge logic refreshes automatically when source data changes.

Basic anti-join to find missing values

1. Convert each list to an Excel Table: click inside the list, press Ctrl+T, confirm the range.
2. Go to Data > Get Data > From Table/Range to load List A into Power Query Editor.
3. Close and load it as a Connection Only query. Repeat for List B.
4. Back in Power Query (Data > Get Data > Combine Queries > Merge), select:
   • Left table: List A query
   • Right table: List B query
   • Match columns: the key column from each table
   • Join kind: Left Anti (rows in List A that have no match in List B)
5. Click OK. The result contains every row from List A that is missing from List B.
6. Click Close & Load to write results to a new sheet.

Change the join kind to Right Anti for the inverse (rows in List B missing from List A), or Inner for matching rows only.

Best for: Recurring comparisons, large datasets, multi-file scenarios. Available in Excel 2016+ on Windows. Excel for Mac supports Power Query from Excel 2019+ (limited features) and fully in Microsoft 365.

If your data lives in separate Excel files rather than separate sheets, Power Query can load both files directly. For a walkthrough of the full file-level comparison workflow, see our guide on how to compare Excel spreadsheets.

Method Comparison — Which to Use When

Use the table below to quickly match your situation to the right method. The excel list comparison scenario that fits most people is COUNTIF — it handles unsorted lists of any size and works in every Excel version.

A note on compare lists in excel for duplicates specifically: Conditional Formatting and COUNTIF are the two methods that natively surface duplicates. VLOOKUP, XLOOKUP, and INDEX/MATCH return only the first match and ignore subsequent duplicates in the lookup range.

The Formula-Free Alternative — Compare XLSX Files in Your Browser

Every method above requires Excel to be open and some level of formula literacy. There is a different approach that works without either: the Diff Checker Chrome extension, which compares XLSX files directly in your browser.

How it works

The extension uses SheetJS (XLSX.utils.sheet_to_txt) to extract the text content from every sheet in your workbook. Each sheet is extracted as tab-delimited text and prefixed with a === SheetName === delimiter so you can see which sheet each row came from. The two extracted texts are then displayed side-by-side in a standard diff viewer, with additions highlighted in green and removals in red — the same interface you use to find the difference between any two text documents.

Key properties — verified from source code

• All processing happens in your browser. The extension's manifest only requests storage, tabs, and clipboardWrite permissions — no network calls to external servers for file processing.
• Multi-sheet support. All sheets in the workbook are extracted and included in the comparison, not just the active sheet.
• Side-by-side and unified view. Switch between both diff display modes via the DiffViewer component.
• DOCX support too. The same extension also handles Word documents via the mammoth library, so you can compare Word documents with the same workflow.
• Optional AI summary. An opt-in AI summary feature sends data to OpenAI only when you explicitly trigger it — it is not automatic.

When to use it instead of formulas

• You received an XLSX file but do not have Excel installed (works on Mac, Windows, Chromebook).
• You want to audit a file without any risk of accidentally editing it.
• Your list comparison is a one-off task and adding COUNTIF helper columns would clutter a shared workbook.
• You are comparing structured data exports — for example, two JSON arrays exported from a database. Our guide on JSON data comparison covers that specific workflow.

Note what the tool does not do: it does not replicate Excel's conditional formatting rules, pivot tables, or chart data. It extracts cell values as text and compares them. For value-level list comparison this is exactly what you need; for format-level or formula-output comparison it shows the computed values, not the formulas themselves.

Frequently Asked Questions

How do I find duplicates between two Excel lists?

Use Conditional Formatting's built-in Duplicate Values rule: select both columns together, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and Excel highlights every value that appears in both lists. For a formula approach, use =COUNTIF($B$2:$B$100,A2)>0 in a helper column — TRUE means the value from List A exists somewhere in List B.

How do I compare two lists in Excel that are on different sheets?

Use a cross-sheet COUNTIF formula: =COUNTIF(Sheet2!$A$2:$A$100,A2)>0. Replace Sheet2 with your actual sheet name. VLOOKUP and XLOOKUP work identically — prefix the lookup range with the sheet name and an exclamation mark. For a full multi-sheet workflow, Power Query merge (Method 8) is the most reliable approach.

Are Excel list comparisons case-sensitive?

No. COUNTIF, VLOOKUP, and XLOOKUP are all case-insensitive by default — "Apple" and "apple" are treated as identical. For a case-sensitive comparison, use the EXACT function in a helper column: =EXACT(A2,B2) returns TRUE only when the case matches exactly. Then run COUNTIF on the helper column results.

What is the difference between VLOOKUP and XLOOKUP for list comparison?

XLOOKUP is the modern replacement available in Excel 365 and Excel 2021. Key advantages: XLOOKUP can look left (VLOOKUP cannot), its built-in not-found argument replaces wrapping in IFERROR, and its syntax is cleaner. For simple presence checking both work equally well. For left-direction lookups or when you want fewer arguments to maintain, choose XLOOKUP.

Can I compare two large Excel lists with 100,000+ rows efficiently?

For very large datasets, Power Query (Method 8) is the most reliable option — it handles millions of rows, works across multiple files, and recalculates automatically on refresh. COUNTIF and VLOOKUP formulas recalculate on every change and can slow significantly at that scale. The Diff Checker browser extension is also an option for one-off comparisons — it extracts all cell data via SheetJS and performs text-based comparison entirely in your browser without formula overhead.