How to Extract Common Values From Two Lists in Excel

Why This Task Matters in Excel

In day-to-day work you rarely work with a single, isolated data set. Far more often you receive information from different departments, systems, or time periods and need to reconcile them. “Extract common values from two lists” is Excel shorthand for answering the deceptively simple question: “What items appear in both lists?”

1. Consider a purchasing manager who holds a master product list of [10,000] SKUs while another team provides a weekly sales file with the SKUs that actually sold. The manager must rapidly identify the overlap to decide which products stay in inventory and which should be discontinued.
2. A finance analyst compares two customer lists—one exported from the ERP system and another from the CRM—to detect missing accounts and duplicate entries before month-end reporting.
3. A human-resources coordinator cross-references the list of employees who completed required training in the Learning Management System against the master employee roster to see who is still outstanding.

In all these scenarios, the analyst is forced to answer “Which items are in both?”. Extracting that intersection can be done manually for small lists, but the moment you deal with hundreds or thousands of rows, manual checks become error-prone and slow.

Excel is purpose-built for this reconciliation because:

• It offers lightning-fast lookup, filter, and array operations.
• Dynamic arrays (Excel 365+) spill unique lists automatically, eliminating the need for helper columns.
• Older versions still provide solid options with INDEX, MATCH, COUNTIF, and Advanced Filter.

If you cannot extract common values efficiently, you risk bad decisions: unnecessary purchases, missed follow-ups with customers, or compliance violations. Mastering this skill sits at the intersection of lookup logic, array thinking, and data cleansing—skills you will reuse for VLOOKUP, Power Query merges, pivot-table building, and even database joins outside Excel.

Best Excel Approach

For users on Microsoft 365, the most efficient solution is a single dynamic-array formula that spills the common items downward automatically:

=FILTER(List1, ISNUMBER(MATCH(List1, List2, 0)))

Why this method?

• FILTER instantly returns only those items where the logical test evaluates to TRUE.
• MATCH with the third argument 0 performs an exact lookup on every element of List1 inside List2, returning numeric positions for matches and #N/A for non-matches.
• ISNUMBER converts those positions into the Boolean TRUE/FALSE array FILTER expects.
• Because Excel 365 formulas are dynamic, the result resizes itself when either source list changes—no copy-down needed.

Use this method when:

• You have access to Excel 365 or Excel for the web.
• Both lists are in standard range or structured table format.
• You prefer a no-macro, no-helper column solution.

If you are on Excel 2019 or earlier, replicate the behavior with an old-school CSE (Ctrl + Shift + Enter) array formula and a SMALL-INDEX extractor:

=IFERROR(
    INDEX($A$2:$A$100,
          SMALL(
               IF(COUNTIF($B$2:$B$100,$A$2:$A$100), ROW($A$2:$A$100)-ROW($A$2)+1),
               ROWS($D$2:D2)
          )
    ),
"")

The logical engine is identical—COUNTIF identifies matches, IF filters non-matching positions, SMALL sorts them into ascending order, and INDEX returns the underlying value.

Parameters and Inputs

• List1 and List2: contiguous ranges, Excel tables, or spilled arrays containing the original values. Data type must be comparable—text to text, number to number; avoid mixing “00123” text with 123 numeric.
• Case sensitivity: MATCH and COUNTIF are case-insensitive by default. For passwords or case-critical codes, use EXACT.
• Duplicates: The basic FILTER/MATCH approach returns duplicates if they exist in List1. Add UNIQUE if you want only one instance.

=UNIQUE(FILTER(List1, ISNUMBER(MATCH(List1, List2, 0))))

• Hidden rows or filtered ranges: FILTER reads hidden values as well. If you want to ignore them, pre-filter with SUBTOTAL or use Table slicers.
• Blank cells: MATCH returns #N/A, so blanks do not pass ISNUMBER; they safely drop out. However, stray spaces lead to mismatched blanks—use TRIM for cleaning.
• Error values inside the source lists propagate into FILTER errors. Prevent by wrapping each list with IFERROR(range,\"\").

Step-by-Step Examples

Example 1: Basic Scenario

Suppose you manage a simple inventory list.

Sheet setup:

• Column A [A2:A11] “Warehouse SKUs”: 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010
• Column B [B2:B7] “Recent Sales SKUs”: 1003, 1006, 1008, 1012, 1015, 1002

Step-by-step:

1. Name the ranges Warehouse_SKU and Sales_SKU via the Name Box or convert each list into an Excel Table (Ctrl + T).
2. Click in cell D2, title it “SKUs in Both Lists”.
3. Enter the dynamic formula:

=FILTER(Warehouse_SKU, ISNUMBER(MATCH(Warehouse_SKU, Sales_SKU, 0)))

and press Enter.
4. Excel spills the result downward: 1002, 1003, 1006, 1008.
5. Add another sale—type 1005 at the bottom of Sales_SKU. The result range automatically expands to include 1005.

Why it works: MATCH compares every SKU from Warehouse_SKU to the list in Sales_SKU. Positions where a match occurs are numeric (1,2,3…), otherwise #N/A. ISNUMBER turns those into TRUE/FALSE so FILTER keeps only TRUE rows.

Troubleshooting:

• If you get #CALC!, ensure dynamic arrays are supported.
• If a duplicate SKU appears twice in Warehouse_SKU, the formula repeats it. Wrap UNIQUE if you want one instance only.

Variation: Reverse the logic to determine items in Sales but not in Warehouse by flipping the ranges inside MATCH.

Example 2: Real-World Application

Scenario: The finance department must reconcile two customer lists before invoicing. One list (List_A) lives on “ERP_Data” sheet, column A [A2:A5000] with account numbers. The other list (List_B) is on “CRM” sheet, column C [C2:C6000]. The goal: extract accounts present in both systems, then pass them to a pivot table for revenue allocation.

Steps:

1. Convert each range to a structured table and name them tblERP and tblCRM for robustness (tables resize automatically).
2. On a new sheet “Reconciliation”, label cell A1 “Matched Accounts”.
3. Enter:

=LET(
     ERP, tblERP[Account],
     CRM, tblCRM[Account],
     FILTER(ERP, ISNUMBER(MATCH(ERP, CRM, 0)))
)

The LET function stores each list once, improving readability and performance.
4. The result spills tens or hundreds of rows. Apply Data > Remove Duplicates if you want truly unique accounts.
5. Create a pivot table from the spilled result, join to sales data via Data Model, and feed the matched accounts into revenue analysis.

Business impact: Without this intersection you risk invoicing a customer that no longer exists in CRM or missing one that does. Automating the reconciliation shrinks closing cycles and eases audit scrutiny.

Integration: Because the output is a live spill, any new or corrected account in either source instantly updates the matched list, keeping the pivot current.

Performance: For [5,000-6,000] rows FILTER runs almost instantly. If the CRM table grows to [100,000] and performance dips, consider moving the logic to Power Query or adding a helper column with XLOOKUP and a Boolean flag.

Example 3: Advanced Technique

Scenario: A data scientist must compare two gene-sequence lists with 200,000 entries each. Requirements:

• Case-sensitive comparison (gene codes where “abc” ≠ “ABC”).
• Return only unique overlap.
• Handle memory efficiently on a standard laptop.

Steps:

1. Store List1 on Sheet “Gene_A”, column A [A2:A200001]. List2 resides on “Gene_B”, column A [A2:A200001].
2. Use a helper function to enforce case sensitivity: EXACT returns TRUE only for exact case matches. Because EXACT cannot operate over entire arrays inside FILTER directly, combine BYROW or MAP (Excel 365 Insider) or craft a lambda.
3. Create a named Lambda function named CaseMatch:

=Lambda(arr1, arr2, BYROW(arr1, LAMBDA(r, SUM(--EXACT(r, arr2))>0)))

4. In Sheet “Results”, cell A2, write:

=UNIQUE( FILTER(Gene_A!A2:A200001, CaseMatch(Gene_A!A2:A200001, Gene_B!A2:A200001)) )

Here CaseMatch returns a Boolean spill where each row is TRUE if that gene exists in Gene_B with exact case. UNIQUE ensures duplicates are removed.

Performance tips:

• Turn off automatic calculation until ready (Formulas > Calculation Options > Manual).
• Close other apps to maximize RAM.
• If still sluggish, load the lists into Power Query and perform an inner join, which streams data rather than loading all at once.

Edge-case handling:

• When either list contains error values (#REF!, #VALUE!) the Lambda must wrap each EXACT call with IFERROR to avoid premature failure.
• Blank gene codes are biologically meaningless—filter them out with a pre-step or extend the Lambda to exclude empty strings.

Professional insight: Although an inner join in SQL or Power Query would be even faster, mastering the dynamic-array version equips analysts who lack database access to perform sophisticated data hygiene inside vanilla Excel.

Tips and Best Practices

1. Convert source lists into Excel Tables; you can then reference columns as tblSales[SKU] which auto-expands.
2. Wrap formulas in LET to avoid recalculating the same MATCH array multiple times—improving speed on large datasets.
3. Append UNIQUE when duplicates in List1 inflate downstream metrics.
4. If you must embed the result list inside other calculations (SUMIF, XLOOKUP), assign it to a named range like Overlap via the Name Manager.
5. For presentable reports, sort the spilled list with SORT or SORTBY.
6. Document your reconciliation steps with cell comments or sheet annotations—future auditors will thank you.

Common Mistakes to Avoid

1. Mixing data types (text “123” versus number 123). Excel treats them as different, producing false negatives. Coerce both lists to text with TEXT or to numbers with VALUE.
2. Using MATCH(List1, List2) without the 0 for exact match; the default approximate match returns unpredictable results in unsorted lists.
3. Forgetting ISNUMBER around MATCH results. FILTER expects TRUE/FALSE, not numeric positions or #N/A.
4. Copying a dynamic-array formula downward. The spill already covers the range; multiple copies trigger a #SPILL! error. Remove extra copies and keep one in the start cell.
5. Attempting to sort the spill range manually. Instead nest SORT: =SORT(FILTER(...)) to keep formulas intact.

Alternative Methods

When FILTER is unavailable or unsuitable, several other options exist.

| Method | Excel Version | Pros | Cons | Use When | | — | — | — | — | — | | INDEX/SMALL array (CSE) | 2007-2019 | Works in older versions, fully formula-based | Requires Ctrl + Shift + Enter; complex to audit | Legacy workbooks, no dynamic arrays | | COUNTIF Flag + AutoFilter | All | Intuitive for novices, no array formulas | Needs helper column and manual filtering | Ad-hoc one-time checks | | Advanced Filter with “Unique records only” | All | No formulas, GUI-driven | Re-run each time data changes | Non-technical users, quick audit | | Power Query Inner Join | 2016+ | Handles millions of rows, case-sensitive option, repeatable refresh | Slight learning curve; adds new query objects | Very large datasets, scheduled ETL | | VBA Dictionary | All | Fast, fully automated macro, case sensitive possible | Requires macro-enabled workbook; security prompts | Heavy automation, repeated nightly tasks |

Switch methods by assessing file size, collaborator skill level, and corporate policy on macros or external data connections. Migrating from formulas to Power Query later is straightforward: load each list as a query, choose Home > Merge Queries > Inner Join, output to a table.

FAQ

When should I use this approach?

Use dynamic FILTER/MATCH anytime you have Excel 365 and need a live, automatically updating intersection between two reasonably sized lists (up to a few hundred thousand rows). It is perfect for dashboards, reconciliation sheets, or any workflow where new rows appear regularly.

Can this work across multiple sheets?

Yes. Simply qualify the ranges with sheet names or table names from other sheets. For instance:

=FILTER(Sheet1!A2:A500, ISNUMBER(MATCH(Sheet1!A2:A500, Sheet2!B2:B700, 0)))

Because arrays can spill across sheet boundaries only within the target sheet, place the formula on the sheet where you want the results displayed.

What are the limitations?

• FILTER is available only in Microsoft 365 and Excel 2021.
• The formula is case-insensitive unless you layer EXACT or Power Query.
• Very large lists (millions of rows) may exceed worksheet row limits or slow calculation—then offload to Power Query or a database.

How do I handle errors?

Wrap input ranges with IFERROR to neutralize pre-existing errors:

=FILTER(List1, ISNUMBER(MATCH(IFERROR(List1,""), IFERROR(List2,""), 0)))

If the final FILTER returns no overlap, Excel shows #CALC! (“No results”). Trap it with IFERROR:

=IFERROR( FILTER(...), "No common items" )

Does this work in older Excel versions?

Not natively. Use the INDEX/SMALL CSE array alternative or an Advanced Filter. For compatibility with both old and new users, consider distributing two versions of the workbook and flagging which sheet is appropriate for each environment.

What about performance with large datasets?

• Turn off “Workbook Calculation” from Automatic to Manual while editing.
• Use LET to store intermediate arrays.
• For 100,000+ rows, Power Query’s merge join or a database view calculates faster and consumes less memory.
• Avoid volatile functions like INDIRECT in the same workbook.

Conclusion

Extracting the common values between two lists is a core data-reconciliation skill that empowers you to validate records, align systems, and make data-driven decisions with confidence. Whether you deploy the elegant one-liner FILTER formula, the classic INDEX/SMALL pattern, or a Power Query join, the concept is the same: isolate the intersection to find trustworthy, shared data. Master this technique and you unlock faster audits, cleaner databases, and smoother collaboration across departments. As a next step, practice integrating the resulting overlap with pivot tables, conditional formatting, or further lookup chains to deepen your Excel proficiency.