Excel Formula to Extract Items from Two Lists - Excel Magic Trick 1597. Is Item NOT in List?

Learn how to create a formula to extract items in List 2 that are not in List 1. Also see second formula to extract items in both lists. Learn about the MATCH & ISNA functions for “NOT in List”. Learn about MATCH & ISNUMBER for “Is In List”. See the Office 365 FILTER Function that mekas extract values from the list easy. Also see a references to the Old School Array Formula Method for extracting items from Two Lists.

[excelisfun]

02:42 Bonus Formula #1... and Bonus Formula #2 at 03:00

[helmanfrow]

Three minutes, thirty-nine seconds' worth of content in as much time. Not a wasted word. Thank you.

[richardhay]

FILTER function continues to shine by making life easier and more efficient!!

[simfinso]

watching Your videos is always a Excel Treat.

[deninsrmic]

Good old array formula and new one, still I am always going back to old school methods and videos of yours where you exercise knowledge of Array formulas of old days in minute detail. Many Thanks Mike.

[Softwaretrain]

Thumbs up even before whatch your training because I'm sure you are the best.

[maciejnejman]

As always incredible portion of formulas and explanation how to use them. Thumb up.

[mohamedchakroun]

Thanks mike wainting for dymamic arrays :-). Even i prefer old formulas because they make us perform a bunch of functions :-) love bonus formulas veryyy

[Luciano_mp]

I still can't understand Microsoft's delay in providing the new formulas, Dynamic Arrays. Thank you Mike!

[jasonm]

Excellent. I was trying to figure out how to do this with FILTER not too long ago.
Needed a list of items in List 1 that were not in List 2.
ISNA/ISNUMBER was the missing ingredient.
Thanks!

[ogwalfrancis]

Comparing two lists is now made easy with Excel's match function, thanks Mike

[wmfexcel]

Array formula is good; Dynamic Array is better
Excel is good; Excel 365 is better! :)

[CeliaAlvesSolveExcel]

Hi, Mike. I still don't have dynamic arrays. I am excited about getting them soon, but I have the feeling it will be challenging to change mindset and be able to use them with ease and efficiency.

[johnborg]

Thanks Mike. Seems I/m the first this time. :) :) Yes I am going to miss those nice large array formulas. Fun building them.

[wayneedmondson]

Hi Mike.. another great video! Here is my slight variation on your legacy solution: =IF(ROWS(I$10:I10)>$I$6, "", INDEX($C$10:$C$21, AGGREGATE(15, 6, 1/ISNA(MATCH($C$10:$C$21, $A$10:$A$28, 0))*(ROW($C$10:$C$21)-ROW($C$9)), ROWS(I$10:I10)))). FILTER will certainly make life easier.. when everyone has it. Thanks for the Saturday FILTER / EXCTRACT fun! Thumbs up!!

[rocky]

Thank you, Mike. Please make video on Data Mining.

[GeorgeAJululian]

Thank you it was very helpful Please if I have a list 3 and list 4 and all list 2/3/4 to be compared with list 1 and extract the names which are not available list 2/3/4

[vhc]

Hi Mike, love this I have a question to expand on this ... how can I use a Dynamic Array formula to combine two separate lists into one and then wrap it inside Sort & Unique?

[apach]

Please help me, i need to extracto or filter the duplicates except the first duplicate between 2 list, for reconciliations, thanks

[RAHULfromIND]

Mike, I ordered your book...Can we extract this by using SMALL function? i tried but couldn't do, pls assist