## Filter common values from three columns

*Article last updated on March 13, 2018*

**Question:** How do I filter values that exists in all three columns?

**Answer:**

Formula in A14:

copied down as far as necessary.

**Named ranges**

List1 (A2:A11)

List2 (B2:B10)

List3 (C2:C11)

What is named ranges?

Download excel example file.

Common values in three columns.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

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

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 lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]

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

### 8 Responses to “Filter common values from three 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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?