Filter common values from three separate columns
Array formula in B15:
Copy cell B15 and paste it to cells below as far as necessary.
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 B15
Step 1 - Prevent duplicates in the list
The COUNTIF function counts values based on a condition or criteria. The first argument $B$14:B14 expands as the cell is copied to cells below. This makes the formula aware of displayed values above the current cell.
COUNTIF($B$14:B14, $B$3:$B$12)
becomes
COUNTIF("Common values in three columns", {"AA"; "BB"; "CC"; "DD"; "EE"; "FF"; "GG"; "HH"; "GG"; "JJ"})
and returns
{0;0;0;0;0;0;0;0;0;0}
Step 2 - Find position of value in array
The MATCH function returns a number representing the position of a value in a list.
MATCH(0,COUNTIF($B$14:B14,$B$3:$B$12)+(((COUNTIF($D$3:$D$11,$B$3:$B$12)>0)+(COUNTIF($F$3:$F$12,$B$3:$B$12)>0))<>2),0)
becomes
MATCH(0,C{0;0;0;0;0;0;0;0;0;0}+((({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE})+({FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}))<>2),0)
becomes
MATCH(0,{1;1;0;1;1;1;0;1;0;1},0)
and returns 3.
Step 3 - Return value
The INDEX function returns a value based on a row and column number.
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))
becomes
INDEX($B$3:$B$12, 3)
and returns "CC" in cell B15.
Download Excel *.xlsx file
Common values in three columns.xlsx
Weekly Blog EMAIL
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
Extract shared values between two columns
Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]
What values are missing in List 1 that exists i List 2?
Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]
Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
Compare two columns and return differences
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
Automate Excel: Update list with new values
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
How to highlight differences in price lists
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
Compare two columns and extract differences
This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]
9 Responses to “Filter common values from three separate columns”
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.
Hello,
I am not certain why the range of $A$13:A13 is used in the countif function... Is this a mistake?
Also, how can I adjust this to check for common values in 15 columns? Is that even possible?
Thanks,
Beth
Beth,
I am not certain why the range of $A$13:A13 is used in the countif function... Is this a mistake?
It makes sure that only unique distinct values are extracted.
See the explanation in this post:
Filter values that exists in all three lists
Also, how can I adjust this to check for common values in 15 columns? Is that even possible?
Yes, add the remaining 12 columns to the formula, using a countif function for each column. See explanation.
Hi Oscar,
Is there a way to do what you did above but without the named ranges?
Thanks
Dan
How can we find common numbers from different sheets and arrange them with column heading and by counting that how many time a found in which Sheet???
REGARDING THE ABOVE QUESTION "Also, how can I adjust this to check for common values in 15 columns? Is that even possible?
Yes, add the remaining 12 columns to the formula, using a countif function for each column. See explanation."
Can you tell me what to insert - for my 3 columns I have =INDEX(A2:A16, MATCH(0, COUNTIF($A$19:A19, A2:A16)+IF(IF(COUNTIF(B2:B16, A2:A16)>0, 1, 0)+IF(COUNTIF(C2:C16, A2:A16)>0, 1, 0)=2, 0, 1), 0)). How do I add further columns into the range? I have tried copy and pasting +IF(COUNTIF(C2:C16, A2:A16)>0, 1, 0)before the = and changing it to d2:d16 but get an error
Louise
Great question!
Array formula in cell B14:
To add a fifth column (Col E) simply add a COUNTIF function.
I tried below formula for the fourth and fifth columns but only one item is listed other NA
What did I do wrong??
Array formula in cell B14:
=INDEX($A$2:$A$11,MATCH(0, COUNTIF($B$13:B13, $A$2:$A$11)+NOT(COUNTIF($B$2:$B$10, $A$2:$A$11)*COUNTIF($C$2:$C$11, $A$2:$A$11)*COUNTIF($D$2:$D$9, $A$2:$A$11)), 0))
To add a fifth column (Col E) simply add a COUNTIF function.
=INDEX($A$2:$A$11,MATCH(0, COUNTIF($B$13:B13, $A$2:$A$11)+NOT(COUNTIF($B$2:$B$10, $A$2:$A$11)*COUNTIF($C$2:$C$11, $A$2:$A$11)*COUNTIF($D$2:$D$9, $A$2:$A$11)*COUNTIF($E$2:$E$9, $A$2:$A$11)), 0))
Albert,
Can you post your formula?
Hi Oscar! I liked your solution. It worked fine for me.
However I have a different challenge now: to make it work for 7 columns, where I may select only some of them to make this analysis. The way the formula is will only return me a value which is common for all the 7columns. Id like to have the chance to select 2 or 3 columns for instance (out of those 7) to apply the search. Of course the selected ones may change on each use. Any idea? Thank you much!