Filter values in common between two cell ranges

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and B6:E9 (Two). There is a much smaller formula if you only need to compare two columns: Extract shared values between two columns

1. Filter common values between two nonadjacent cell ranges - Excel 365

This example demonstrates a short dynamic array formula that works only in Excel 365. It compares two cell ranges B2:D4 and B6:E9 and extracts values in common between these two cell ranges.

The formula returns values to cell G3 and cells below automatically, Microsoft calls this behavior for spilling. A #SPILL error is returned if one or more cells are populated making it impossible for the formula to show all values.

Excel 365 formula in cell G3:

Note that the formula does not compare rows or records only single cell values, however, this article covers that topic: Filter shared records from two tables

Explaining formula in cell G3

Step 1 - Rearrange values

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL(B2:D4)

becomes

TOCOL({"AA","BB","CC";"DD","BB","EE";"FF","CC","GG"})

and returns

{"AA";"BB";"CC";"DD";"BB";"EE";"FF";"CC";"GG"}

Step 2 - Count values based on criteria

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF(B6:E9,TOCOL(B2:D4))

becomes

COUNTIF(B6:E9,{"AA";"BB";"CC";"DD";"BB";"EE";"FF";"CC";"GG"})

and returns

{2;0;1;0;0;0;0;1;0}

Step 3 - Extract values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(TOCOL(B2:D4),COUNTIF(B6:E9,TOCOL(B2:D4)))

becomes

FILTER({"AA";"BB";"CC";"DD";"BB";"EE";"FF";"CC";"GG"}, {2;0;1;0;0;0;0;1;0})

and returns

{"AA";"CC";"CC"}

Step 4 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(TOCOL(B2:D4),COUNTIF(B6:E9,TOCOL(B2:D4))))

becomes

UNIQUE({"AA";"CC";"CC"})

and returns {"AA";"CC"}.

2. Filter common values between two ranges - Excel 2019

Array formula in B12:

Explaining formula in cell B12

Step 1 - Identify values shared by both cell ranges

The COUNTIF function counts values based on a condition or criteria, in this case, it compares values between the cell ranges.

COUNTIF($B$6:$E$9, $B$2:$D$4)>0

becomes

COUNTIF({"HH", "II", "JJ", "KK";"AA", "MM", "NN", "OO";"AA", "QQ", "RR", "SS";"TT", "II", "VV", "CC"}, {"AA", "BB", "CC";"DD", "BB", "EE";"FF", "CC", "GG"})>0

becomes

{2,0,1;0,0,0;0,1,0}>0

and returns

{TRUE, FALSE, TRUE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE}

Step 2 - Prevent duplicates

The next COUNTIF function counts values based on a condition or criteria, in this case, we take into account previously displayed values in order to prevent duplicates from showing up in our list.

The first argument in the COUNTIF function B$11:$B11 expands as the cell is copied to cells below, this makes the formula aware of values above the current cell.

COUNTIF($B$11:B11,$B$2:$D$4)=1

becomes

COUNTIF("Common values in range One and Two", {"AA", "BB", "CC";"DD", "BB", "EE";"FF", "CC", "GG"})=1

becomes

{0,0,0;0,0,0;0,0,0}=1

and returns

{FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE}.

Step 3 - Add arrays

If at least one of the COUNTIF functions return TRUE then the array returns TRUE or the equivalent value 1.

(COUNTIF($B$6:$E$9,$B$2:$D$4)>0)+COUNTIF(B11:$B$11,$B$2:$D$4))=1

becomes

({TRUE, FALSE, TRUE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE}+{FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE})=1

becomes

{1,0,1;0,0,0;0,1,0}=1

and returns

{TRUE, FALSE, TRUE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE}.

Step 4 - Replace TRUE with coordinates

The following IF function returns the corresponding coordinate if boolean value is TRUE. FALSE returns "" (nothing).

IF(((COUNTIF($B$6:$E$9,$B$2:$D$4)>0)+COUNTIF(B11:$B$11,$B$2:$D$4))=1,(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,"")

becomes

IF({TRUE, FALSE, TRUE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE},(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,"")

The ROW and COLUMN functions return the row(s) and column(s) of a cell range. Adding these two numbers creates a unique number for each cell in the cell range.

IF({TRUE, FALSE, TRUE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE},(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,"")

becomes

IF({TRUE, FALSE, TRUE;FALSE, FALSE, FALSE;FALSE, TRUE, FALSE},{2.33333333333333, 2.25, 2.2;3.33333333333333, 3.25, 3.2;4.33333333333333, 4.25, 4.2},"")

and returns

{2.33333333333333,"",2.2;"","","";"",4.25,""}

Step 5 - Get the smallest value in array

The MIN function returns the smallest number in array ignoring blanks and text values.

MIN(IF(((COUNTIF($B$6:$E$9,$B$2:$D$4)>0)+COUNTIF(B11:$B$11,$B$2:$D$4))=1,(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))

becomes

MIN({2.33333333333333,"",2.2;"","","";"",4.25,""})

and returns 2.2.

Step 6 - Identify value in cell range

IF(MIN(IF(((COUNTIF($B$6:$E$9, $B$2:$D$4)>0)+COUNTIF(B11:$B$11, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, "")

becomes

IF(2.2=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,$B$2:$D$4,"")

becomes

IF(2.2={2.33333333333333, 2.25, 2.2; 3.33333333333333, 3.25, 3.2; 4.33333333333333, 4.25, 4.2},$B$2:$D$4,"")

becomes

IF({FALSE,FALSE, TRUE;FALSE, FALSE,FALSE; FALSE,FALSE, FALSE},$B$2:$D$4,"")

and returns

{"","","CC";"","","";"","",""}.

Step 7 - Join text strings in array

The TEXTJOIN function returns values concatenated ignoring blanks in array.

TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$6:$E$9, $B$2:$D$4)>0)+COUNTIF(B11:$B$11, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, ""))

becomes

TEXTJOIN("", TRUE, {"","","CC";"","","";"","",""})

and returns "CC" in cell B12.

3. Filter common values between two ranges

If your Excel version doesn't contain the TEXTJOIN function then use the following array formula.

Array formula in B12:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Get Excel *.xlsx file

Filter common values from two ranges.xlsx

Filter common values from two rangesv2.xlsx

4. Filter values in common between two cell ranges - UDF

I tried the array formula in this post: Filter common values between two ranges using array formula in excel to extract common values between two cell ranges. 40000 random cell values in each cell range.

As you might have guessed, the array formula is too slow. Sheet2 contains 40000 random text strings in cell range A1:J4000, sheet3 also contains 40000 random text strings in cell range A1:J4000

This udf creates a list of common cell values between the two cell ranges:

User defined function

Function Common_Values_2_Ranges(rng1 As Variant, rng2 As Variant) As Variant

Dim Value1 As Variant
Dim Value2 As Variant
Dim temp() As Variant
Dim Test As New Collection

ReDim temp(0)

rng1 = rng1.Value
rng2 = rng2.Value

On Error Resume Next
For Each Value1 In rng1
 If Len(Value1) > 0 Then Test.Add Value1, CStr(Value1)
Next Value1
On Error GoTo 0

On Error Resume Next
For Each Value2 In rng2
 If Len(Value2) > 0 Then Test.Add Value2, CStr(Value2)
  If Err Then
   temp(UBound(temp)) = Value2
   ReDim Preserve temp(UBound(temp) + 1)
  End If
  Err = False
  Test.Remove Value2
Next Value2
On Error GoTo 0

Common_Values_2_Ranges = Application.Transpose(temp)

End Function

How to add the user defined function to your workbook

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor
5. Select sheet1
6. Select cell range A1:A5000
7. Type =Common_Values_2_ranges(Sheet2!A1:J4000, Sheet3!A1:J4000) into formula bar and press CTRL+SHIFT+ENTER
8. Make sure you save your workbook with the file extension *.xlsm so you can use the UDF the next time you open the same workbook.

Get the Excel file

Filter-common-values-in-two-ranges-udf1.zip

Recommended blog post:

Compare two lists of data: Filter common row records in excel

Compare category

Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

What values are missing in List 1 that exists i List 2?
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

How to highlight differences and common values in lists
This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

Excel categories

Comments (2)