How to Find the Intersection of Two Datasets in Excel

Finding where two datasets overlap — the values that exist in both lists simultaneously — is one of the most practical data tasks in Excel. Whether you're reconciling inventory lists, matching customer IDs, or comparing survey responses, identifying the intersection tells you exactly what the two datasets share.

Excel doesn't have a single button labeled "find intersection," but it offers several reliable methods depending on your data structure, Excel version, and comfort with formulas.

What "Intersection" Means in Data Terms

In set theory, the intersection of two datasets is the collection of values that appear in both sets. If Dataset A contains [apple, banana, cherry] and Dataset B contains [banana, cherry, mango], the intersection is [banana, cherry].

In Excel, this translates to: which rows, values, or records exist in both ranges at the same time?

The answer changes depending on whether you're matching on a single column, multiple columns, or entire rows.

Method 1: COUNTIF — The Workhorse Approach

The COUNTIF function is the most accessible way to flag matching values between two lists.

How it works: For each value in Dataset A, COUNTIF checks whether that value appears anywhere in Dataset B. A result greater than zero means it's in the intersection.

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

Place this formula in a helper column next to Dataset A. Any row returning 1 or higher is a match — it exists in both datasets. Filter or sort by that helper column to isolate the intersection.

Best for: Single-column comparisons, quick visual checks, users comfortable with basic formulas.

Method 2: MATCH with ISNUMBER — Cleaner Boolean Output

For a TRUE/FALSE result that's easier to filter programmatically, combine ISNUMBER with MATCH:

=ISNUMBER(MATCH(A2, $B$2:$B$100, 0)) 

This returns TRUE if the value in A2 exists anywhere in the B column range, and FALSE if it doesn't. It's slightly more precise than COUNTIF because it confirms existence without counting duplicates, which matters when duplicate handling affects your logic.

Best for: Clean Boolean outputs, feeding results into conditional logic, or use inside larger nested formulas.

Method 3: VLOOKUP or XLOOKUP — Retrieving Matched Data 🔍

If you need to pull data from Dataset B for matching rows in Dataset A — not just flag the match — VLOOKUP or its modern replacement XLOOKUP is the right tool.

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

This looks up the value from A2 in column B and returns the corresponding value from column C. Rows returning "No Match" are not in the intersection; everything else is.

XLOOKUP advantages over VLOOKUP:

• Searches left-to-right or right-to-left
• Returns a custom value for no match (no error handling needed)
• Available in Excel 2021 and Microsoft 365

Method 4: Conditional Formatting — Visual Intersection Highlighting

When you want to see the intersection without adding helper columns, Conditional Formatting highlights matching cells directly.

1. Select Dataset A's range
2. Go to Home → Conditional Formatting → New Rule
3. Choose "Use a formula to determine which cells to format"
4. Enter: =COUNTIF($B$2:$B$100, A2)>0
5. Set a highlight color and confirm

Every cell in Dataset A that also appears in Dataset B will be highlighted immediately. This is a purely visual method — no new data is generated.

Best for: Quick audits, presentations, or when you don't want to modify the spreadsheet structure.

Method 5: Power Query — For Large or Repeating Workflows ⚙️

For larger datasets or processes you'll repeat regularly, Power Query's Merge feature is purpose-built for this kind of join.

1. Load both datasets as queries (Data → Get & Transform Data → From Table/Range)
2. Select one query and choose Home → Merge Queries
3. Match the key columns between the two datasets
4. Choose "Inner Join" — this returns only rows where both datasets match, which is exactly the intersection

Power Query preserves the steps as a refreshable process, so when your source data updates, rerunning the query reflects the new intersection instantly.

Best for: Datasets with thousands of rows, multi-column key matching, recurring reports, or users who prefer a visual workflow over formulas.

Matching on Multiple Columns

When your intersection depends on more than one field — for example, a customer ID and a date both having to match — your approach needs adjustment.

With formulas, concatenate the key columns into a single comparison string:

=COUNTIF($D$2:$D$100, A2&B2) 

Where column D contains a concatenated version of Dataset B's key columns (=B2&C2). This creates a compound key that must match on all specified fields simultaneously.

Power Query handles multi-column matching natively through its merge interface without requiring manual concatenation.

The Variables That Determine Which Method Works for You

No single method is universally correct. The right approach depends on factors specific to your situation:

• Dataset size — formulas slow down noticeably on tens of thousands of rows; Power Query scales better
• Excel version — XLOOKUP isn't available before Excel 2021, and Power Query has evolved significantly across versions
• Whether duplicates matter — COUNTIF counts all occurrences; MATCH just confirms existence
• What you need as output — a flag, a value, a highlighted cell, or a full extracted table
• Single vs. multi-column keys — compound matching requires different logic in formulas vs. Power Query
• How often the data changes — static datasets suit formulas; dynamic or refreshed data suits Power Query

The method that's genuinely most efficient for finding your intersection depends on the structure of your specific datasets, which fields define a "match" in your context, and how you intend to use the results afterward. 📊