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/
✅KZbin Channel: 👉 / simonsezittraining
✅LinkedIn: 👉 / simon-sez-it
🔔Subscribe to our channel:
kzbin.info...

[@SimonSezIT]

**GET FOUR FREE COURSES!** ► www.simonsezit.com/four-free-courses We have additional free Excel training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u Check out our FREE 300+ hour training course playlist here ➡ 🔥kzbin.info/aero/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU

[@LC-og4by]

Exactly what I was looking for. Thank you for making a potentially complicated process clear and easy to follow. I’ll be watching again.

[@songedeau]

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 😊

[@il_Presepe]

This was stellar! Clear. Detailed. To the point. Thank you very much for making and sharing this video.

[@fivetimesyo]

Oh my goodness! You are lifesavers! Wonderful narrator and brilliant process. Thank you so much.

[@HKRotich]

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 ?

[@ibrahimahmad72]

I have been watching your videos, for 3 days And there's no words to be described, You're just amazing, Thank you!

[@SimonSezIT]

You're welcome! We're so glad you're enjoying our videos. 😊 🔥Check out our FREE 300+ hour training course playlist here 👉 kzbin.info/aero/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@mimi3118]

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]

Glad you think so! Thanks for leaving as comment ;-)

[@MarioMarioD80]

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

[@adinamohamed9806]

Thank you so much for the very easy guide. The explanation was clear, precise and step by step. Looking forward for more videos.

[@SimonSezIT]

Glad you think so, Adina. Thanks for watching!

[@lalatorvike6077]

You’re a life saver. Very clear and precise.

[@SimonSezIT]

Glad it helped Lala 😊

[@ExcelEssentials_]

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!

[@labeed2652]

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!

[@Cora_la_flor]

This is so helpful. Thank you for being so clear and precise.

[@rubenvargas4233]

This is the most comprehensive video explaining what to do. Thank you so much.

[@SimonSezIT]

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 👉 kzbin.info/aero/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@tenajiawilliams5702]

You've explained this so clearly! Thank you!!

[@SimonSezIT]

Glad you liked it! 😊

[@wiredblitzen]

After looking at so many other tutorials this was the best articulated. Thank you soooo much

[@jokorah181]

Very clearly explained. the video covered all that was required. Thank you so much!!

[@SimonSezIT]

Thank you and you're very welcome!

[@HD-ct5ne]

This was extremely useful. Thanks a lot!!

[@jacquelineloz4572]

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 😊

[@user-gu3zq1pb9o]

Thank you for clearly explaining how to do a VLOOKUP :)

[@beyobeya]

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.

[@RobertTheTexan2]

Thanks for the VLOOKUP life preserver. You are so appreciated!

[@SimonSezIT]

Thank you so much 😀

[@VP-yp8ip]

This is really helpful. Now I’m able to compare my reports more effectively.

[@SimonSezIT]

Thank you so much 😀

[@nicollettegyles6859]

Exactly what I needed!!! Thank you so much

[@SimonSezIT]

Wow, thanks! Glad it was helpful. 🤩 🔥Check out our FREE 300+ hour training course playlist here 👉 kzbin.info/aero/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@BESTMACKY]

Wow! This will be very helpful. Thanks for sharing.

[@SimonSezIT]

You’re welcome 😊 Thanks for you watching!

[@janellebryant2136]

Great tutorial!! THANK YOU!!!

[@SimonSezIT]

You are very welcome! Thanks for watching :-)

[@amitabhpaul1501]

This Really helped me a lot, great video, Thank you.

[@SimonSezIT]

You are very welcome! Glad it was helpful. 🙂 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

[@lmg4293]

Really appreciate the way you teach!

[@SimonSezIT]

Glad to hear that!

[@teresabourke7551]

Excellent tutorial. Very clear and concise. Thank you.

[@SimonSezIT]

Thank you so much for your kind words! 😊 We have intermediate and advanced Excel 2021/365 training too, if you need it 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@omarsanchez9709]

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

[@vigneshv8445]

Excellent one this one is what i really wanted...Thank u

[@SimonSezIT]

Happy to learn it was helpful, Vignesh :-)

[@BESTMACKY]

This will be very helpful, thanks for sharing.

[@SimonSezIT]

Happy to help!

[@halilpotoku3395]

the best explanation about vlookups,

[@SimonSezIT]

Glad it was helpful!

[@FaceSatchel]

Thanks for this. Really helped.

[@SimonSezIT]

Glad it was helpful! 🤩 Learn more Microsoft Excel Tips and Tricks here ➡️ kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc

[@jasonwilson1818]

Great lesson and video, thank you!

[@SimonSezIT]

You're welcome!

[@luisvillasenor6081]

Best Tutorial ever O.O!!!!!!!!!!!!!!

[@lseh]

I love this! Thanks!!!

[@SimonSezIT]

So glad! Thanks for watching :-)

[@DashCLO7]

Thank you very much for this. This is indeed useful.

[@SimonSezIT]

Glad you enjoyed it. Thanks for watching! 🙂

[@alexrosen8762]

Excellent tutorial

[@SimonSezIT]

Thanks Alex!🙂

[@believerinyhvh8586]

Love the explanation but do you have a spread sheet so I can try the formula as you explain?

[@minalc4598]

Thank you for the help

[@EdsonNascimentoEN]

Great tutorial thanks for sharing.

[@SimonSezIT]

Glad it was helpful! Thanks for watching 😃

[@mexifrida]

Thank you!

[@floydguel4238]

Thank you !!

[@osmankhalidmohammed]

Really helpful

[@DivyaSingh-hf4cq]

Thank you for sharing the video

[@SimonSezIT]

Thanks for watching, Divya!

[@ursimha]

Thank You!!

[@MsChari40]

Thank you. I did it!!!

[@SimonSezIT]

Congrats!

[@Chrisbajs]

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.

[@rose_shimmerling]

Awesome! Thanks.

[@SimonSezIT]

You're welcome!

[@zakaurrehmankhan8185]

Found very helpful

[@SimonSezIT]

Glad you think so, Zaka Ur Rehman Khan :-)

[@wue42]

great work! well done! now I see it clear ... ;-)💯

[@SimonSezIT]

Glad you enjoyed it. Thanks for watching! 🙂 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u

[@Commander_Chief]

Thank you

[@dennohism]

Great!

[@AhmadNubliMuhammad]

superb. thank you

[@SimonSezIT]

You are most welcome 😊

[@hazelamican2596]

Thank you for this can I request for sample excel template touse for practice thanks

[@muralimohan3392]

Thanks

[@ilonadraznina5425]

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.

[@seihasky9840]

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!

[@MayWongMY]

very useful

[@SimonSezIT]

Glad you it was helpful. Thanks for watching! 🙂 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

[@kpogiorbaritema3852]

thank you GOD Bless you

[@SimonSezIT]

Glad we could help!

[@mykola_kanyuk]

brilliant

[@SimonSezIT]

Thanks! Delighted you think so!

[@joannachung3642]

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.

[@mir22v]

good explanation, but image is very blurry.

[@ellahrasonabe9821]

Great

[@floragill1200]

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!

[@peachcookie6624]

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?

[@dubaidiary8368]

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

[@HussamQazi]

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?

[@merinarajkarnikar5946]

Wow ❤❤

[@SimonSezIT]

Glad you enjoyed it. Thanks for watching! 🙂

[@sadigon2007]

great

[@mariberggren9147]

What if we are searching for approximate values instead of exact?

[@duahkwaku8286]

How do we get the data to practice with it.

[@ubaidillahmuhammad20]

nice. put practiced file in the description video ...

[@suejaz8085]

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..

[@thandonkalitshana2024]

What if you want to compare more than 1 Collum

[@tomclark2203]

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.

[@RobertTheTexan2]

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. 😊

[@AnilAnvesh]

if function is much easier than vlookup comparison. if(Table1[column1]=Table2[Column1],,"Missed") ❤

[@SimonSezIT]

Thanks for sharing!

[@minhtho3]

Not really, in case there are different ID on the same row.

[@usmaniqbal1836]

Please share excel practice file

[@Holmesisaok]

Thank you baby jesus for this help

[@mikechafe419]

I would say this task is not complete. What about the data on Employee list 2 that is not on Employee List 1?

[@farooqtahir538]

Have you heard about IFNA function?😂.This can do this thing more easily😅

[@notexactlyrocketscience]

1:03 for non-Anglos who don't do long-winded presentation intros

[@realitycorner3365]

The easiest is to filter the NA

[@kylieturner4021]

Very Cool

[@SimonSezIT]

Thanks for watching!

[@hishamfathy6051]

great thank you

[@johnq3543]

Thank you

[@gaylemusgrove872]

🥰Thanks!

[@SimonSezIT]

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 👉 kzbin.info/aero/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU 🚀 Level up your Excel skills with free training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support! #SimonSezIT #Excel

[@kvelez]

Thanks

[@SimonSezIT]

You are very welcome 🙂

[@emmanueloseimensah6002]

Thank you