Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)

Learn how to Compare 2 Lists, Extract Items In List 2 That are NOT in List 1:
1. (00:12) Problem Description and Intro To Topic
2. (01:57) Example 1: Method 1: MATCH function Helper Column, Sort Z to A, Copy and Paste
3. (04:15) Example 2: Method 2: Advanced Filter and Criteria Area with Empty Field Name and Logical Formula Criteria
4. (07:59) Example 3: Method 3: Automat Process with Excel 2010 or later Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX, AGGREGATE, SUMPRODUCT. This Array Formula does not require Ctrl + Shift + Enter.
5. (15:29) Why you should NOT use IFERROR on Data Extract Array Formulas
6. (17:17) Example 4: Method 3: Automat Process with Excel 2007 or before Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX, SMALL, SUMPRODUCT. This Array Formula DOES require Ctrl + Shift + Enter.
7. (18:25) Example 5: Method 2: Extract E-mails in List 1 that are NOT in List 2.
8. (19:35) Caveat about Advanced Filter: Be sure to NOT have data below Extract Area (because it will be deleted when you run Advance d Filter.
9. (20:24) Example 6: How to Extract Items that are in BOTH lists: Learn how to slight change the formula for all three methods. Use ISNUMBER rather than ISNA.

Related Videos for Comparing Two Lists and Extracting Records:
Other Excel Methods for Comparing Two Lists:
Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)
Excel Magic Trick 1229 Extract Items NOT in List 1: Conditional Format Unique Values & Sort by Color
Excel Magic Trick 1441: Power Query to Extract Items In List 1 That Are NOT In List 2
Excel Magic Trick 1442: DAX Query & EXCEPT Function to Extract Items In List 1 That Are NOT In List 2

[kbell]

Clear explanations and practical examples made this seemingly complex concept easy to understand and significantly enhanced my knowledge and confidence using Excel.

[Writepranab]

Excellent. Used it to create a table to put values from a drop down menu where a value can be picked for only once. Thank you very much for your video.

[NullisNaN]

My favorite yet.
I usually just put my values in A, and a description in B like "firstlist" & "secondlist" then in C do a if countif formula. I really like your last solution.

[FsoOmar]

ACE Video, for learning the AGGREGATE Function, and ARRAYS.

[pmsocho]

Advanced filter method - so cool!
And thanks for pointing out that deletion below the extracted list issue :)

[agesch]

Until now I have compated my two lists with the help of vlookup to and back. I loved the advanced filter solution for this issue, I will try it asap in real. Thank you Mike!

[robertjones]

I like the way you *Booped Cell H39&40 with the format painter! :D

[MySpreadsheetLab]

The advanced filter method was great! Thanks Mike!

[messinaalejandro]

Hi Mike Excellent video, I humbly add that could use a table (Ctrl + t) joining both lists names Column 1, List Column2 (as List1 and List2), then make a Pivot Table rows by dragging names and names in values ​​and use the count function, and use filter in row <2. Sorry for my English  I speak spanish greetings from Argentina

[Sal_A]

Wish you made the dynamic array formula more robust by making the ranges dynamic i.e using index. and without any helper columns. Excellent video btw.

[alandouglas]

Wow, great video mate, I had to figure this one out on my own... got it

[anwarahmed]

Amazing, awesome tips from girvin
+ everything is free from Mike.
thank u so much
Anwar from yemen

[BritonWells]

Many solutions for this! can also use IF(ISNA(VLOOKUP)) then filter

[HITOKIRIpl]

Mega thx, that array was exactly what I needed

[johannkristjonsson]

Thank you. This will help me in  my work.

[soneeric]

Thank you for all your videos. You are a great trainer.
Please how can I Compare two lists A and B in different workbooks? List A is the standard. If there are differences in List B, we copy the correct value from A to list B.

[Manaz]

Nice!  Just looks like a bit involved. I compare lists almost on a daily basis and put them side by side and just use Conditional Formatting, Unique Values, then sort via color and send the colored ones to the top (depending on the column I want to get the values for).

[The-Bright-Stuff]

Thank you for showing. After I entered the formulas I get rows which are less then my count number but are returning zeros. What can I add to formula to remove zeros?

[islamicknowledge]

Hi I have invoice no with multiple collection amount how to know the exact value of outstanding on the invoice no.

[andrewcragg]

Very cool, what do I do if the original list keeps growing?