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

Array formula in B12:

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.

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.

Get Excel *.xlsx file

Filter common values from two ranges.xlsx

Filter common values from two rangesv2.xlsx

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Sort a range based on value frequency

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Filter common values from three separate columns

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 […]

Extract unique distinct text values containing string in a range

The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Extract unique distinct values sorted based on sum of adjacent values

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

Filter duplicate values and sort by corresponding date

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]