Filter common values between two 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
Table of Contents
1. Filter common values between two nonadjacent cell ranges - Excel 365
Excel 365 formula in cell G3:
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
Compare category
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.