Excel Formula to Compare Two Lists - Excel Magic Trick 1596. Is Item in List?
HTML-код
- Опубликовано: 8 сен 2024
- Download Excel File: excelisfun.net...
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.
Bonus Formula at 03:36...
This video saved my life. Thank you for creating such easy to follow guides.
You are welcome!!!!
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!!
Awesome formulas, Wayne!!!!! Go Team!!!!
This was the best, quick and only video that helped me figure out my formula. Thank you!
Thanks Mike.. 🌟 🌟 🌟
You are welcome, Salim!
Thank u so much Mr. Mike I’m finally back so so sorry I’ve be through rough days
I am glad that you are back! I hope things will be okay for you : )
Thanks Mike for these vids, they are most appreciated. Never a dull moment with your teachings.
Glad it is not dull for you, Simoiya!!!!
Thank you so much ❤❤❤❤❤
You are welcome!!!
Thanks mike. The bonus formula was great!!!
Glad you liked it all and the bonus : )
Thank you Mike. Quite useful, your explanation is very clear as allways.
Glad it is clear and fun for you, Enrique!!!!!!!
Thank you! I learned something new today!
Glad this helps, Explicit!!! Thanks for your support!
Awesome video and a very clever formula Mike.
Thanks, Michal!!!
Thanks Mike
You are welcome, Awesome Dave Bowman : ) : ) : )
Great video. As always. Thanks!
Thanks longtime Teammate, Jan!!!
Good job Mike...
You are welcome, Ayobami!!!
Excellent in short time
Glad it helped!
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 :)
Yes, indeed, COUNTIF and COUNTIFS is a short way to do this... I think many years ago I timed COUNTIF=0 and ISNA(MATCH)), but I can't remember how it came out... For some reason I settled on ISNA-MATCH...
Great video Mike, thank you!
You are welcome, most esteemed Teammate Chris Mancinelli!!!
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.
It does depend on the goal, but Conditional Formatting? Conditional Formatting is totally cool and great when you want to pick out and just "see" the items most quickly! Thanks for that hot tip, Pavel : )
Great video! I never knew that there is a function that could show easily that an item is NOT in list! Genial!
Glad it is Genial for you, Evgenia M!!! Thank you for your comments and support : )
Thanks Mike..👍👍👍
You are welcome, Luciano!
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
You are welcome a lot, Lamees!!!!! I have a few more interesting videos on this topic coming out in the next week : ) : )
@@excelisfun Thanks a lot 😍
Thanks
You are welcome!
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?
Works a treat - many thanks
Nice use of ISNA! Awesome!
Thanks, MF Wong : ) : )
nice explanation
Glad it was nice for you, Steven Nye : )
"Match" Formula is Easy.I will go for it.
Go for it, Phone Excel!!!!
Thanks for sharing, it's very interesting this vidéo.
Glad you like it!!!!
high Mr. mark well done , bravo
Glad you like it!!! Thanks for the Bravo, ahmed!!
Awesome 🙏
Thank you, dhun P!!!!!
thanks
very nice trick
Glad you like it, Ahmed!!!! You are welcome : )
Mike, I’m a complete newbie to Excel. Is there a series of videos that begin with the very basics? Thank you very much
Yes there is. Here it is: ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Thanks for your support with your comments, thumbs ups and Sub , Harry : )
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.
Love that solution, Teammate Geert!!!!!
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 😂😂
It is TRUE that many people can't say exactly what they want, but know you know there are only two possible questions: Item from List X NOT in List Y, use ISNA and MATCH; Item from List X IS in List Y, use ISNUMBER and MATCH.
Nice reviwwww and nice to this bonus formulas :-)
Thanks, Mohammed!!!!!!!
Beautiful. Thanks
You are welcome, Joshua!!!!
God bless you man
Glad you like the videos, Zeeshan!
Great Magic trick, thanks Mike
Thanks, Ogwal : ) : )
The bonus trick z amazing, Am looking forward to the Xlookup instead of Vlookup
@@ogwalfrancis I am away from my video filming equipment and my Excel Insider until Monday : 0 … But on Monday afternoon I will post a video, AND I CANNOT WAIT!!!!!! : )
Am very very curious for it, Really waiting for it.
Thumbs up :)
Thanks, Teammate Malina : )
Very helpful trick👍
But how can you give us a bonus when we know that all things are for free😄
You get a bonus anyway : )
what if list one contains for example a 17 digit number but list two only uses 8 of those same numbers. Is there a way to match the two list or does list 2 have to contain all 17 digits for a perfect match?
Thank you, best trick
Can we use countif function?
Yes, you can use COUNTIFS too : )
Hi. For the library list example. Can you format the result so if it’s “false” the “false” and the name of the book is highlighted so it sticks out better in such a long list?
Absolutely! I am not at a computer with Excel, but I think this would work: =IF(ISNA(MATCH(LookupValue,LookupRange,0)),TRUE,FALSE&", "&LookupValue)
Hello Dear Mike,
Could you do a tutorial about minimum cost and method Voguel in excel in base a one example whit the complement named excel's solver of forme automatic, please !
Best regards **
Thanks a lot of the attention lended"
I have great videos on this in my Business analytics playlist (section 12, videos # 57 to 63)
Pleas help support with your comments and thumbs up, THX 4 :)
@@excelisfun ok, very good your vídeos and it's very great !
It doesn't work with Names and numbers....
Hi Sir please help me I want to know how I can found the day or month through formula with number like 33 means 2nd Feb
Then 125 means ???
I have no idea!?!?! For back and forth dialog to get Excel solutions, try this site: mrexcel.com/forum
HEY! I have been working to improve my excel and was wondering if i would run into issue using this >> =IF(C13=$A$13:$A$31,"","Lead")
You will get an error. You can't compare a cell with an array. You can use this =IF(ISNUMBER(MATCH(C13,$A$13:$A$31,0)),"","Lead")
Well, you have an array operation in the first argument. If you do not have Office 365, you might have to use Ctrl + Shift + Enter.
Okay! I'm a user of 365 and did not realize that. I will try to have better questions and make sure I can't answer them myself next time. Thanks guys!
@@Johnnydizzlefoshizle : )
👌👍👍
Thanks for the thumbs ups, Zaigham!!!
Another easiest thing. Finding duplicates
Glad it is easy for you, Dinesh!!! Thanks for watching and having fun : )
Now for a video using the new XLOOKUP for the bonus! I called MS to find out why as insider I do not yet have it. They said within 3 days I would!!! We'll See!LOL
I am away from my recording equipment and my computer with my Excel and Insider until Monday... I can't wait to do a XLOOKUP video then. I was not told about this new function, Microsoft do not keep me in the loop as much as I would like. But I can't wait to check this out... : )
@@excelisfun I learned about it this am from a short Bill J. video he posted last night. I'm not suure if the "x" means no more VL and no more I/M OR if it means a, "cross between these to classic lookup methods. LOL Either way it appears to be powerful, efficient, fun and awesome!!! Looking forward to your take on it and to getting it myself so that I can play!!
@@richardhay645 I can't wait!!!!
Iferror(if(match(item, item list, 0),"y"),"n")
Thanks, jai!!!
My friend you are too fast. Lolest
HAHA