How to Use VLOOKUP to Compare Two Lists
Ғылым және технология
✅GET FOUR FREE COURSES ⏩ www.simonsezit.com/four-free-...
In this VLOOKUP tutorial, we look at how to use the VLOOKUP function to compare two lists or columns of data in Excel.
If you want to save yourself a huge amount of time in Excel when reconciling data, it’s worth taking the time to learn how to use VLOOKUP to compare two lists or columns of data.
For example, maybe you are an accountant who keeps a record of all invoices generated by a client. The client also keeps their own lists of invoices. At the end of the year, the client sends their invoice log to their accountant for reconciliation.
In this scenario, the accountant could print out both lists and spend time with a big yellow highlighter, marking invoices that are missing from the client log. A more efficient approach would be to use VLOOKUP to run a comparison.
🌍 Get unlimited training with Simon Sez IT’s 150+ courses ⏩ www.simonsezit.com/courses/
🚀 Level up your skills with a free 30 day trial to our complete training library of 8,500+ videos ⏩ www.simonsezit.com/ssit-first...
Check out our Excel 2021 tutorials for beginners playlist here
✅ • Excel 2021/365 Trainin...
Check out our FREE 300+ hour training course playlist here ➡️
🔥 • Simon Sez IT Long Trai...
💻Watch more popular tutorials from Simon Sez IT:
✅Microsoft Office 2021 and Office 365 Training Tutorials playlist
👉 • Microsoft Office 2021 ...
✅Microsoft Office 2019 Training Tutorials for Beginners playlist
👉 • Microsoft Office 2019 ...
✅Excel 2021 Tutorials for Beginners playlist 👉 • Excel 2021/365 Trainin...
✅Microsoft Word Training Tutorials for Beginners, Newbies and Dummies 👉 • Microsoft Word Trainin...
✅ PivotTable training here 👉 • Pivot Tables - All you...
💬Stay in touch!
✅SimonSezIT.com: 👉www.SimonSezIT.com/
✅StreamSkill.com: 👉StreamSkill.com/
✅KZread Channel: 👉 / simonsezittraining
✅LinkedIn: 👉 / simon-sez-it
🔔Subscribe to our channel:
kzread.info...
Пікірлер: 144
**GET FOUR FREE COURSES!** ► www.simonsezit.com/four-free-courses We have additional free Excel training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzread.info/head/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u Check out our FREE 300+ hour training course playlist here ➡ 🔥kzread.info/head/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU
This was stellar! Clear. Detailed. To the point. Thank you very much for making and sharing this video.
Exactly what I was looking for. Thank you for making a potentially complicated process clear and easy to follow. I’ll be watching again.
Oh my goodness! You are lifesavers! Wonderful narrator and brilliant process. Thank you so much.
Been trying to figure this out for YEARS. It just clicked! Thank you for making this very clear.
@SimonSezIT
Жыл бұрын
That is great, thanks for sharing 😊
Pro tip! When you have a spreadsheet with a lot of columns: just make sure that the column with the lookup value is simply left of the column you want to search in. The Vlookup function can only search to the right!
Brilliant!! I'm an Accountant, tracing missing invoices from Revenue Authority was my biggest problem.
@user-cz3mz1kc1y
Жыл бұрын
Is it the right function to use with bank reconciliation in excel please ?
Happy to have opened this post as this helps a lot as i 'm just new in my new role at the office and i need a lot of reporting/comparing cases to do! This is much appreciated!❤
@SimonSezIT
3 ай бұрын
Glad you think so! Thanks for leaving as comment ;-)
in addition you can use the function 'ifna(function, return value when na)' - or 'iferror(function, return value when error)' which will also cover div/0 etc. also useful if you rather want to leave the cell empty
I have been watching your videos, for 3 days And there's no words to be described, You're just amazing, Thank you!
@SimonSezIT
6 ай бұрын
You're welcome! We're so glad you're enjoying our videos. 😊 🔥Check out our FREE 300+ hour training course playlist here 👉 kzread.info/head/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzread.info/head/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZread Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzread.info ♥️ We appreciate your support!
You’re a life saver. Very clear and precise.
@SimonSezIT
2 жыл бұрын
Glad it helped Lala 😊
This is so helpful. Thank you for being so clear and precise.
Thank you for clearly explaining how to do a VLOOKUP :)
Thank you so much for the very easy guide. The explanation was clear, precise and step by step. Looking forward for more videos.
@SimonSezIT
2 жыл бұрын
Glad you think so, Adina. Thanks for watching!
This is the most comprehensive video explaining what to do. Thank you so much.
@SimonSezIT
6 ай бұрын
Thank you for your kind words! I'm so happy that the video was able to provide you with the information you needed. 😊 🔥Check out our FREE 300+ hour training course playlist here 👉 kzread.info/head/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzread.info/head/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZread Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzread.info ♥️ We appreciate your support!
Thanks for the VLOOKUP life preserver. You are so appreciated!
@SimonSezIT
Жыл бұрын
Thank you so much 😀
This was extremely useful. Thanks a lot!!
Very clearly explained. the video covered all that was required. Thank you so much!!
@SimonSezIT
Жыл бұрын
Thank you and you're very welcome!
You've explained this so clearly! Thank you!!
@SimonSezIT
Жыл бұрын
Glad you liked it! 😊
After looking at so many other tutorials this was the best articulated. Thank you soooo much
I usually use "IFERROR" function for that, but "ISNA" looks helpful too, and it's another tool to use... always good to know more than one way to handle things. Thank you for this informative tutorial!
@SimonSezIT
Жыл бұрын
Happy to help!
the best explanation about vlookups,
@SimonSezIT
2 жыл бұрын
Glad it was helpful!
Excellent tutorial. Very clear and concise. Thank you.
@SimonSezIT
7 ай бұрын
Thank you so much for your kind words! 😊 We have intermediate and advanced Excel 2021/365 training too, if you need it 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZread Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzread.info ♥️ We appreciate your support!
This is really helpful. Now I’m able to compare my reports more effectively.
@SimonSezIT
3 ай бұрын
Thank you so much 😀
This helped me a lot in my accounting job. Issues with operations team backdating job numbers without letting accounting know. Therfore running this report using v look up helped call them out
Best Tutorial ever O.O!!!!!!!!!!!!!!
Exactly what I needed!!! Thank you so much
@SimonSezIT
9 ай бұрын
Wow, thanks! Glad it was helpful. 🤩 🔥Check out our FREE 300+ hour training course playlist here 👉 kzread.info/head/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzread.info/head/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZread Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzread.info ♥️ We appreciate your support!
Great tutorial!! THANK YOU!!!
@SimonSezIT
2 жыл бұрын
You are very welcome! Thanks for watching :-)
Thank you for the help
Wow! This will be very helpful. Thanks for sharing.
@SimonSezIT
2 жыл бұрын
You’re welcome 😊 Thanks for you watching!
Excellent one this one is what i really wanted...Thank u
@SimonSezIT
2 жыл бұрын
Happy to learn it was helpful, Vignesh :-)
Easy to understand the steps
This will be very helpful, thanks for sharing.
@SimonSezIT
2 жыл бұрын
Happy to help!
Really appreciate the way you teach!
@SimonSezIT
8 ай бұрын
Glad to hear that!
This Really helped me a lot, great video, Thank you.
@SimonSezIT
3 ай бұрын
You are very welcome! Glad it was helpful. 🙂 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
Thank you for explaining this, everyone else always uses technical terms and it doesn’t connect in my head.
@SimonSezIT
Жыл бұрын
Glad you like them! Thanks for watching 😊
Great lesson and video, thank you!
@SimonSezIT
Жыл бұрын
You're welcome!
Thank you!
Thank you very much for this. This is indeed useful.
@SimonSezIT
9 ай бұрын
Glad you enjoyed it. Thanks for watching! 🙂
Thank you !!
Thank You!!
I had similar experience last year wherein I needed to compare a list of 400+ tickets our team has against a list from our Finance team. I did the same process (VLOOKUP, IFERROR, and Conditional Formatting) which was great but I noticed that some items can be in file A but not in file B, or vice versa. That vice versa thing was a headache for me. Looking back at the situation, I think I may just did an overthinking and that I could have just done the same process in both directions.
Thank you
I love this! Thanks!!!
@SimonSezIT
Жыл бұрын
So glad! Thanks for watching :-)
great thank you
Thanks for this. Really helped.
@SimonSezIT
Ай бұрын
Glad it was helpful! 🤩 Learn more Microsoft Excel Tips and Tricks here ➡️ kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc
Great tutorial thanks for sharing.
@SimonSezIT
Жыл бұрын
Glad it was helpful! Thanks for watching 😃
Thanks
Thank you for sharing the video
@SimonSezIT
Жыл бұрын
Thanks for watching, Divya!
if function is much easier than vlookup comparison. if(Table1[column1]=Table2[Column1],,"Missed") ❤
@SimonSezIT
Жыл бұрын
Thanks for sharing!
@minhtho3
6 ай бұрын
Not really, in case there are different ID on the same row.
Excellent tutorial
@SimonSezIT
2 жыл бұрын
Thanks Alex!🙂
Really helpful
Great!
Awesome! Thanks.
@SimonSezIT
2 жыл бұрын
You're welcome!
Love the explanation but do you have a spread sheet so I can try the formula as you explain?
thank you GOD Bless you
@SimonSezIT
Жыл бұрын
Glad we could help!
Found very helpful
@SimonSezIT
Жыл бұрын
Glad you think so, Zaka Ur Rehman Khan :-)
superb. thank you
@SimonSezIT
Жыл бұрын
You are most welcome 😊
great work! well done! now I see it clear ... ;-)💯
@SimonSezIT
4 ай бұрын
Glad you enjoyed it. Thanks for watching! 🙂 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzread.info/head/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u
Fantastic video. Only gripe I have, is that sheet number two (Employees 2) is called almost exactly the same as the named datarange (Employees2). Can be a bit confusing.
brilliant
@SimonSezIT
3 жыл бұрын
Thanks! Delighted you think so!
Thank you for this can I request for sample excel template touse for practice thanks
awesome!
@SimonSezIT
19 күн бұрын
Glad it was helpful! 🤩 Learn more Microsoft Excel Tips and Tricks here ➡️ kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc
Great
very useful
@SimonSezIT
5 ай бұрын
Glad you it was helpful. Thanks for watching! 🙂 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
great
Hello, thank you for useful video, maybe you can add how I can compare 2 lists and add missing data? For example, if I have "Taylor Gross+ in the Column 1 but don't have it in column 2, I want to add "Taylor Gross" to column 2.
Wow ❤❤
@SimonSezIT
9 ай бұрын
Glad you enjoyed it. Thanks for watching! 🙂
What do you see as the benefits of using Lookup vs Conditional formatting the cell ranges based on based on ? For example, using something like "=A1Sheet2!A1" as the conditional formatting formula.
@pabs000
Жыл бұрын
Very nice !!
@pabs000
Жыл бұрын
Also, very nice is the fact you can move the columns around from the 1st or 2nd dataset in either left or right direction without breaking the formula!
Sorry. It works perfect on worksheet but "wrong formula" in conditional formatting. I tested separately in two separate columns but got the same result. I am using Excel 365. May I know why? Many Thanks.
What if we are searching for approximate values instead of exact?
Hello I want the two tables side by side on one sheet, sort both table for the employee id to be smaller to largest and use vlookup function to find the employee id that exists in one table but does not in the other table and vice Versa. What I am struggling with is this: Let’s say there are 10 employee ids in table1 and 9 in table2. And employee id no. 4 is missing in table2 I want to add a row in table 2 to indicate that the entry is missing and move employee ids from 5-10 a row below to match with the entries in table1 How do I do this?
How do we get the data to practice with it.
good explanation, but image is very blurry.
Can someone share me a link through which I can learn stock count or like I have a huge sayad which is present stock available and physically count stock with quantity and how to workout to find the variance. Thank you in advance for your help
Hi. I a number that I need to find in a column. which is clubbed amount of 2 numbers or more in another table. How I can find that numbers? Like I want to find what numbers are adding up to make $3000. is there any way?
My Motion looks nothing like that....How do I color code?
QUESTION: I have 2 lists that were generated by 2 different databases. one formulates names DOE, JANE SARAH...the other formulates each name in a different column and every name written as Doe Jane Sarah instead of upper case. So I CONCAteNTATED the names from the list that had the name separated in columns, then I did =UPPER() to make the names all upper case. I then copied and pasted as a "paste special" so that it copies as the final value instead of the formula. so the names look exactly the same. Then i tried conditional formatting and couldn't find any matches, even though I know there were at least 10 just visually. then, to add to the mystery, I hand typed one name in the same format as the DOE, JANE SARAH... and this is the only name that matches with conditional formatting or VLOOKUP. I've diddled around in a few cells just to see if there are extra spaces or something and it doesn't seem like it. I copied it into googlesheets and tried conditional formatting and got the same weirdness. What am I doing wrong? why are these names that visually look 100% identical not finding their matches? help!
nice. put practiced file in the description video ...
Thank you baby jesus for this help
i have a problem.. all is good when i did the formula on first cell. but when i drag to have the formula in subsequent rows.... it just copies my answer on first row.. to all subsequent rows..
What if you want to compare more than 1 Collum
Please share excel practice file
I have watched, let's just say, "my fair share" Excel VLOOKUP videos, but they did not click click with me. This video clicked. It all made so much more sense than the other "experts" out there. The first time through I was like Ohhhh that's how I need to view the Lookup Value. You gave criteria for it and you made it MAKE SENSE. So I was pretty stoked when my VLOOKUP actually did what I wanted it to do. Then I got my data cleaned up with your other tips and tricks. Lastly, I do appreciate the context you provided for your examples. If there was an award for "Best VLOOKUP Video on the Interweb" You should certainly win that award, and I feel so strongly about that, I would likely throat-punch anyone who disagreed with that sentiment. :-) JK. Probably. Also a very pleasant voice. Not an "I'm a super brain and you're a moron voice, just a pleasant, confident, knowledgeable voice. Thank you for saving me from VLOOKUP hell. You are Awesome Sauce, no doubt about it.
@SimonSezIT
Жыл бұрын
Oh wow, we are so stoked by what you just said. Thank you so much, as much as we are glad that you found our tutorial/s helpful. 😊
I would say this task is not complete. What about the data on Employee list 2 that is not on Employee List 1?
I tried this on two datasets I am trying to compare and every result is #N/A. I know there are values that match between both. I verified all cells are formatted as text. Not sure what else I can do to resolve the error. Stumped.
@pabs000
Жыл бұрын
Try, =IF(A1Sheet2!A1,"Missing Record",""). Thanks to Seiha Sky for suggesting.
1:03 for non-Anglos who don't do long-winded presentation intros
Have you heard about IFNA function?😂.This can do this thing more easily😅
The easiest is to filter the NA
Very Cool
@SimonSezIT
2 жыл бұрын
Thanks for watching!
Thank you
Thank you. I did it!!!
@SimonSezIT
2 жыл бұрын
Congrats!
🥰Thanks!
@SimonSezIT
6 ай бұрын
You are very welcome and thank you so much for your support! We really appreciate it🙂 🔥Check out our FREE 300+ hour training course playlist here 👉 kzread.info/head/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzread.info/head/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzread.info/head/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzread.info/head/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZread Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzread.info ♥️ We appreciate your support! #SimonSezIT #Excel
Thanks
@SimonSezIT
22 күн бұрын
You are very welcome 🙂
Thank you