Excel Formula to Compare Two Lists - Excel Magic Trick 1596. Is Item in List?

Learn how to create a formula to compare two lists. See Two Formulas. One formula to ask question “Is Item in List?”. One formula to ask the question “Is Item NOT in List?”. Learn how to use the MATCH, ISNUMBER and ISNA functions. See a bonus formula that uses the VLOOKUP Function.

[motorboatski]

This video saved my life. Thank you for creating such easy to follow guides.

[Realdanika]

This was the best, quick and only video that helped me figure out my formula. Thank you!

[wayneedmondson]

Hi Mike.. great stuff. Another approach.. for your first example: =COUNTIF($A$13:$A$31, C13)>=1 or =COUNTIF($A$13:$A$31, C13)<>0 would return the same TRUE and FALSE answers. For the second example: =COUNTIF($A$11:$A$103, C11)<>1 or =COUNTIF($A$11:$A$103, C11)=0 would do the same. You could also use: =SUMPRODUCT(--($A$13:$A$31=C13))>=1, etc. Fun to solve the same problem in different ways with different combinations. Thumbs up!!

[simoiyahector-morales]

Thanks Mike for these vids, they are most appreciated. Never a dull moment with your teachings.

[enriquedominguez]

Thank you Mike. Quite useful, your explanation is very clear as allways.

[johnborg]

Thanks mike. The bonus formula was great!!!

[ExplicitBeats]

Thank you! I learned something new today!

[ismailismaili]

Thank u so much Mr. Mike I’m finally back so so sorry I’ve be through rough days

[michalroesler]

Awesome video and a very clever formula Mike.

[evgeniam]

Great video! I never knew that there is a function that could show easily that an item is NOT in list! Genial!

[mohamedchakroun]

Nice reviwwww and nice to this bonus formulas :-)

[pavelpellar]

Great formula, I personally prefer conditional formating (eg highlight True cells red) as it's visible at a first sight, but of course depends on the purpose why we need to compare those lists.

[youssoufcisse]

Thanks for sharing, it's very interesting this vidéo.

[Powerpuffgirlllll]

Thank you so much for the clear explanation. Is there a formula that will return the text results and not numbers, also a summary of the data please?

[GeertDelmulle]

Great tricks.
I would go further and turn the Booleans into numbers (double neg) and then use cel formatting to hide the zeros.
So visually the user is only confronted with the exceptions. Then one can dynamically filter the report table to get just the list of exceptions.

[pmsocho]

Thanks for the video. I used to do this with VLOOKUP but now I use COUNTIF. COUNTIF can additionally count values so we can see if there are duplicates :)

[briandennehy]

Great stuff Mike. I always find this question is a bit of a head melter at the best of times - which items in list 1 are not in list 2, which items in list 2 are not in list 1, which items are in both lists, which items that are not in both lists etc etc. Usually your boss can't phrase the question properly which confuses the matter even further 😂😂

[simfinso]

"Match" Formula is Easy.I will go for it.

[lameessy]

Wonderful magic trick ❤
I work in a book store. We used to make a list in excel for sending books as samples. It was very difficult to know if these books are back or not. We forget. We get confused. We count the books, we check the excel list again and again, with ths magic trick we save alot of time and we dont waste energy... Thanks a lot

[harrystamatis]

Mike, I’m a complete newbie to Excel. Is there a series of videos that begin with the very basics? Thank you very much