My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Extract Values Present in Two Lists

You are here: Home / Excel Formulas / Extract Values Present in Two Lists
by

Comparing two lists is easily done with Power Query, but maybe you prefer to use a formula to extract values present in two lists.

Before dynamic array formulas this was a daunting formula to write, but the new FILTER function makes it dead easy. In this tutorial I’ll show you both methods.

text in both lists

Note: The FILTER function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. Excel 2019 will not have the Dynamic Array functions.

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Watch the Video

Subscribe YouTube

 

Extract Values Present in Two Lists Formula

The data in cells B4:B8 has the named range List1 and cells C4:C8 are List2. These names are used in the formula.

Looking at the FILTER and COUNTIF formulas first:

FILTER(List1,COUNTIF(List2,List1)>0)

In English it reads; filter List1 where the count of the items in List2 that match List1 is greater than zero.

That is, the count will be 1 or greater if the items are present in both lists.

COUNTIF returns an array of values {1;0;1;1;1}. In fact, the >0 argument could be omitted from the formula as the ones and zeros will act as the Boolean values for FILTER's 'include' argument.

I then wrapped the formula in the UNIQUE function to ensure there were no duplicates and used the SORT function to return a sorted list, with the final formula being:

=SORT(UNIQUE(FILTER(List1,COUNTIF(List2,List1)>0)))

Extract Values Present in Two Different Sized Lists

This technique isn’t limited to lists of text that are the same size. In the example below you can see the lists contain numbers and the list, Values2, is bigger than Values1:

extract values present in two lists of different sizes

The formula is:

=SORT(UNIQUE(FILTER(Values1,COUNTIF(Values2,Values1)>0)))

Interestingly, COUNTIF returns the following array {1;1;0;2;2}, and again the >0 can be omitted and FILTER will include the correct values i.e all values except 30. In fact, FILTER will treat any value, positive or negative as the equivalent of TRUE for the purpose of the 'include' argument. Of course, zero will be treated as FALSE.

Extract Values Present in Two Lists Excel 2019 and Earlier

If you’re not fortunate enough to have Office 365, then you can use the equivalent array formula from Oscar Cronquist below, entered with CTRL+SHIFT+ENTER in cell J4 and copied down:

=INDEX(Values1, SMALL( IF( COUNTIF(Values2,Values1)* NOT( COUNTIF($J$3:J3,Values1)), ROW(Values1)- MIN( ROW(Values1))+1,""),1))

Note: The formula above does not sort the values in ascending order.

Alternatively, you can use Power Query to compare two lists.

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Dynamic Arrays Posts

excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
Excel LET Function
Excel LET Function allows you to declare variables and intermediate calculations inside of the formula improving readability and performance.
list_first_monday_date_in_each_month
With the DATE and WEEKDAY functions we can easily list first Monday date in each month. With EOMONTH you can easily switch to the last Monday of each month.
The new Excel XLOOKUP Function replaces the need for VLOOKUP, HLOOKUP, INDEX & MATCH and more. It’s available in Office 365.
Excel Hash 2019
Excel Hash Competition 2019 – dynamic playoffs table uses Icons, XOR, Dynamic Arrays and Linked Pictures to create an integrated solution.
Excel Sorted Dynamic Unique List
Excel Sorted Dynamic Unique Lists have never been so easy now that we have dynamic array formulas.
excel dynamic arrays
The new Excel Dynamic Arrays will change the way you work with Excel formulas. As well as a host of new functions, CTRL+SHIFT+ENTER is no longer required.

More Excel Formulas Posts

List Filenames in a Folder
Two easy ways to list file names in a folder in Excel.
Excel REGEX Functions
New Excel REGEX functions make light work of manipulating text and are far easier than the old MID/LEFT/RIGHT/FIND etc. functions.
Modern Excel Functions
10 must know modern Excel functions that simplify common tasks and make you the go-to expert in your office.
Excel GROUPBY and PIVOTBY Functions
Excel GROUPBY and PIVOTBY functions enable you to pivot and summarize your data without the need for PivotTables.
LABS.GENERATIVEAI Function
The Future is Here: Excel's LABS.GENERATIVEAI function brings ChatGPT inside a formula.
Excel SUMPRODUCT Function
The Excel SUMPRODUCT function is one of Excel's most versatile and underutilized functions allowing for flexible aggregations of data.
Summarize Months to Quarters
3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions
Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively
How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges
Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.


Category: Tag:

Reader Interactions

Comments

  1. Robert H. Gascon

    Hi Mynda,
    I downloaded your sample file and entered these similar non-array formulas:
    1. To extract text in both lists, the formula in F4, copied down rows, is:
    =IFNA(LOOKUP(2,1/(COUNTIF(List1,”<="&List1)=AGGREGATE(15,6,
    COUNTIF(List1,"0)*(COUNTIF(F$3:F3,List1)=0)),1)),
    List1),””)
    2. To extract values in both lists, the formula in K4, copied down rows, is:
    =IFNA(LOOKUP(2,1/(Values1=AGGREGATE(15,6,
    Values1/((COUNTIF(Values2,Values1)>0)*(COUNTIF(K$3:K3,Values1)=0)),1)),
    Values1),””)

    Reply

      • Robert H. Gascon

        Welcome, Mynda. For an unknown reason, some parts of my first formula were not pasted. Let me paste it again here: 

        =IFNA(LOOKUP(2,1/(COUNTIF(List1,"<="&List1)=AGGREGATE(15,6,
COUNTIF(List1,"0)*(COUNTIF(F$3:F3,List1)=0)),1)),
List1),"")
        Reply

  2. Richard Jones

    Great tutorial. However, in the workbook I downloaded the 1st filter shows A, B and G not A, E and G like your tutorial? When I take B out of list 1 and change it with something else, it works. But B seems to want to be included in both lists!

    Reply

  4. Sumit Guha

    Hi,
    I like to know what are the best books of Excel- VBA Macro and also MS Office VBA Macro programming.
    Power BI Desktop and MS Power Quary.
    Dashboard
    MS Access database, MySQL, SQL Database and Server.
    Pls suggest me books of above topics.

    Thanks.
    Sumit Guha.

    Reply

    • Catalin Bombea

      Hi Sumit,
      We do not have reviews on any book unfortunately, you will have to compare them online.
      One thing I can say: no matter which one you buy, you will find valuable information in all of them.
      Make sure to start with an appropriate level, don’t start with advanced books if you don’t know the basics.
      Cheers,
      Catalin

      Reply

  5. Sunny Kow

    Hi Mynda

    It will be interesting to see your solution to extract the unmatched items from both lists.
    i.e.B, C, F and 30, 75, 90 from your examples above.
    I would most probably merge the two lists and then filter them like you did but with the count=1.

    Sunny

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *