Filter common values from three columns in excel
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
Related blog posts
- Merge three columns into one list in excel
- Extract largest values from two columns using array formula in excel
- Comparing two columns and sum unique values using array formula in excel
- Count unique distinct values in three columns combined in excel
- Identify missing values in two columns using excel formula







Leave a Reply