Find the Intersection of Multiple Excel Lists with a Dynamic Array Formula
Question details
Find the unique values that occur in every one of several Excel lists using 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.
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.
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.
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.
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"))
Press Enter. The shared values spill into cells below the formula; if no values match, the cell displays “No common values.”
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).

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. 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. 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. 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. Filter the matches: Select the helper column and use Data > Filter. Open the filter dropdown and exclude blank cells to display the shared values.

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.




