Function Problems

Find the Intersection of Multiple Excel Lists with a Dynamic Array Formula

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Find the unique values that occur in every one of several Excel lists using a dynamic array formula.

Find the Intersection of Multiple Excel Lists with a Dynamic Array Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to compare multiple lists and return their intersection without VBA or an add-in.
Observed behavior
The desired result is a dynamic list of values present in every source list.
Before you start

Confirm that your Excel version supports LET, UNIQUE, VSTACK, and FILTER. Adjust the example ranges to contain only populated cells, and leave enough empty cells below the formula for the results to spill.

Solution 1Recommended

Use a Dynamic Array Formula to Find Values in All Three Lists

Use this formula to return each value shared by three lists once and update the results when the source values change.

The formula stacks the lists, creates a unique set of candidates, and keeps candidates found in each source range. COUNTIF comparisons are not case-sensitive, and wildcard characters in source values can affect matching.

1
Arrange the lists

Place the three lists in A2:A20, B2:B20, and C2:C20. Adjust the ranges in the formula to match your populated cells, with no blank cells inside the ranges.

2
Enter the formula

Select an empty cell outside the source lists and enter: =LET(a,A2:A20,b,B2:B20,c,C2:C20,u,UNIQUE(VSTACK(a,b,c)),FILTER(u,(COUNTIF(a,u)>0)*(COUNTIF(b,u)>0)*(COUNTIF(c,u)>0),"No common values"))

3
Check the results

Press Enter. The shared values spill into cells below the formula; if no values match, the cell displays “No common values.”

4
Add more lists if needed

For a fourth list in D2:D20, add d,D2:D20 to LET, add d to VSTACK(a,b,c,d), and multiply the FILTER condition by (COUNTIF(d,u)>0).

Use a Dynamic Array Formula to Find Values in All Three Lists
If the formula cannot spill: If Excel displays #SPILL!, clear any occupied cells in the intended output area. If a function is not recognized, check whether your Excel version supports it.
Find common values in WPS Spreadsheet

Compare Three Lists with a Helper Column in WPS Spreadsheet

Use COUNTIF in WPS Spreadsheet to mark values that appear in all three lists, then filter the helper column to show the matches.

  1. 1. Open your workbook: Open the workbook in WPS Spreadsheet and place the three lists in columns A, B, and C. Adjust the example ranges below to contain only populated cells.
  2. 2. Add a helper formula: In D2, enter =IF(AND(A2<>"",COUNTIF($B$2:$B$20,A2)>0,COUNTIF($C$2:$C$20,A2)>0,COUNTIF($A$2:A2,A2)=1),A2,""). Adjust the range endpoints to match your lists.
  3. 3. Fill the formula down: Drag the fill handle from D2 down alongside the first list. Each matching value appears only at its first occurrence in column A.
  4. 4. Filter the matches: Select the helper column and use Data > Filter. Open the filter dropdown and exclude blank cells to display the shared values.
Identify shared values without VBA or an add-inShow each matching value once with a helper formulaOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Will duplicate values appear more than once in the Excel result?

No. UNIQUE creates one candidate per value before FILTER checks whether it appears in every list.

What happens if the lists have no values in common?

The example FILTER formula displays “No common values.” You can replace that text in the formula with another message.

Why does the formula show #SPILL!?

The output area may contain data or another obstruction. Clear the cells below the formula so Excel can display the dynamic array.

Does COUNTIF distinguish uppercase from lowercase?

No. COUNTIF is not case-sensitive. It also treats characters such as * and ? as wildcards, so values containing those characters require special handling for literal matches.

Can I compare more than three lists?

Yes. Add each range as a LET variable and include it in VSTACK, then add a corresponding COUNTIF condition to the FILTER calculation.