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.
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.
Watch the Video
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:
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.
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),””)
Nice, Robert. Thanks for sharing!
Welcome, Mynda. For an unknown reason, some parts of my first formula were not pasted. Let me paste it again here:
Thanks. It was probably caused by the HTML encoding characters incorrectly.
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!
Oops! I’ve fixed the file now. The formula had the COUNTIF ranges back to front. Thanks for letting me know.
In the file provided the formula in E4 is wrong. Lists have to be switched in COUNTIF.
Sorry, Frank. I’ve fixed it now. Thanks for letting me know.
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.
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
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
Hi Sunny,
Yes, not as easy to extract unmatched items. I’d probably use Power Query 🙂
Mynda