Extract Common Values in Two Lists

Just assume that you have two lists containing values/words in the few cells, and you want to extract the same or common values/words from the two lists into another separate list; then you might think that it’s not a big deal; because you would prefer to manually extract the same or common values/words from the two lists into another separate list without any need of the formula.

Then congratulations because you are thinking right, but let me include that it would be a big deal to extract the same or common values/words from the two lists including multiple cells into another separate list and doing it manually would be a foolish attempt, because there are 90% chances that you would 100% get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting the same or common values/words from the two lists into another separate list would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

 General Formula


The Following formula would help you compare and extract the same or common values/words from the two lists into another separate list.

=FILTER(Table1,COUNTIF(Table2, Table1))

extract common values from two lists1

This Formula is based on the FILTER and COUNTIF function, where the Table1 (A2:A7) and Table2 (B2:B7) are the named ranges, the table in the range D2:D7 is the common list containing the common elements or values by comparing both Table1 and Table2.

Syntax Explanations:


Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to executing the same or common values/words from the two lists into another separate list:

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • COUNTIF: It is a statistical function that contributes to counting the number of cells to meet specific criteria
  • List: In this formula, the list represents the two lists present in the excel worksheet to execute the common values
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and to separate them from the rest of the elements.

Let’s See How This Formula Works:


The FILTER function takes an array of values as input and an “include” parameter to filter the array based on a logical expression or value.

The array is provided in this example as the named range “Table1“, which contains all values in the range A2:A7. The COUNTIF function, which is nested inside FILTER, provides the included argument:

=FILTER(Table1,COUNTIF(Table2, Table1))

extract common values from two lists1

COUNTIF is set up with Table1 as criteria and Table2 as the range. Because if the eight criteria values are given to the  COUNTIF, then as an array, it would also return eleven results like the following:

{1;1;0;1;0;1;0;1;0;1;1}

extract common values from two lists1

Keep it into your notice that the 1’s correspond to the Table2 items, which also appear in the Table1.

As with the aid of “include” argument this array is delivered to the FILTER function directly:

=FILTER(Table1,{1;1;0;1;0;1;0;1;0;1;1})

extract common values from two lists1

Using the values provided by COUNTIF, the FILTER function efficiently filters the Table1. The values except zero are preserved, and the values associated with zero are removed.

The list spread into the range D2:D7 is the final result consisting of an array of values common in both Table1and Table2.

More Examples


The raw results from COUNTIF are used as the filter in the above algorithm. This works because Excel considers any non-zero number to be TRUE and any zero value to be FALSE. If COUNTIF returns a count larger than one, the filter will continue to function normally.

You can use “>0” to force TRUE and FALSE results explicitly, like as follows:

=FILTER(Table1,COUNTIF(Table2,Table1)>0)

extract common values from two lists1

Remove duplicates From Common Values


Nest the formula inside the UNIQUE function to remove the duplicates, just like as follows:

=UNIQUE(FILTER(Table1,COUNTIF(Table2,Table1)))

extract common values from two lists1

Sort Common Values


Just nest the formula in the SORT function to sort results:

=SORT(UNIQUE(FILTER(Table1,COUNTIF(Table2,Table1))))

extract common values from two lists1

Extract values missing from Table2


You can reverse the logic for getting the output values in Table1 missing from Table2, like in the follows:

=FILTER(Table1,COUNTIF(Table2,Table1)=0)

extract common values from two lists1

Related Functions


  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel Sort function
    The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …
  • Excel UNIQUE function
    The guide demonstrates how to use the UNIQUE function and dynamic arrays in Excel to create unique values.The syntax:=UNIQUE(array, [by col], [once exactly]) …
Related Posts

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Repeating Character n Times in Excel
Repeating Character n Times in Excel1

In daily life, we can use repeated characters to indicate the magnitude or priority of something. For example, a single “!” indicates a minor case, double “!!” indicates a medium case, and triple “!!!” indicates a high case, and so ...

Calculate Cumulative Loan Interest in Excel
calculate cumulative loan interest excel1

What is Cumulative Loan Interest? When comparing different types of loans, many people want to know what is Cumulative Loan Interest. The sum of all interest payments you've made on a loan is referred to as cumulative interest. Different lenders ...

Calculate Cumulative Loan Principal Payments in Excel
Calculate Compound loan principal payments excel1

How do you Calculate Cumulative Loan Principal Payments in Excel? There are a few different ways to calculate this information, but one way has been proven to be accurate more often than others. One option is to use the CUMPRINC ...

Trap Error or Replace Error by Specific Value with IFERROR function
Trap Error or Replace Error by Specific Value with IFERROR function1

We often use Excel formulas in our work life, and we may encounter this situation that the formula throws an error and finally an error like #DIV/0! Is displayed in the cell. In today’s tutorial, we will introduce you the ...

Calculate Compound Interest in Excel

This article will show you how to calculate compound interest in Excel. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest. One of the simplest ...

Count Attendance and Absence with COUNTIF function
Count Attendance and Absence1

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance. Using a week as an example, we use the following example to show ...

Sidebar