HOW TO: compare two columns in Excel using VLOOKUP
Тәжірибелік нұсқаулар және стиль
If you want to save yourself a huge amount of time when reconciling data, learn how to use VLOOKUP to compare two lists or columns of data.
I’ll also share with you the key things you need to avoid to ensure your VLOOKUP works correctly.
---Read the full blog post and DOWNLOAD the files Sharyn’s using here: excelatwork.co.nz/excel-using...
🔔 Become an Excel at Work Insider (100% free) and be FIRST TO KNOW about new videos, blogs and special offers: www.excelatwork.co.nz/2017/06... 🔔
❤ VERSION FRIENDLY ❤
Try this with Excel 2010, Excel 2013, Excel 2016, Excel 2019 and Excel for Office 365.
Sharyn’s using the desktop version of Excel for Office 365 as part of the Office 365 Business Premium subscription.
#ExcelTutorial #ExcelTips #ExcelBeginner #ExcelIntermediate #ExcelatWork #ExcelVLOOKUP
Пікірлер: 85
Let Sharyn help you learn how to use VLOOKUP to compare two lists or columns of data (download the exercise file to follow along).
I hope you know you just helped me 3 years later! Thank youuuu
@ExcelatWork
Жыл бұрын
I'm so glad! Thank you for letting me know!! 😀
Kia Ora! I'm a Kiwi living in Sweden working on a project where this information is exactly what I need. Its great to find it, but after 5 years away it is great to have the quick lesson on my accent :) Thank you very much!!
@ExcelatWork
Жыл бұрын
Kia ora Marty! I hope you're doing well up in Sweden. Lovely to read your message, and so great the video helped you. I get quick a few interesting comments about my Kiwi accent. Not all of them as nice as yours LOL! Take care, Nga mihi nui. Sharyn
I think you were the first person that explained this in a way that makes sense 👌🏽
@ExcelatWork
3 жыл бұрын
Thank you! I'm so glad you found it helpful 😀
@shoepm
3 жыл бұрын
Big laugh for Ms Lagah
This is most professional how to video for microsoft excel - she talks like a journalist - and the background music in the intro was something else
@ExcelatWork
3 жыл бұрын
Thanks Chris! So glad you enjoyed it 😀
Finally found a tutorial that easy to understand. Big thanks!
@ExcelatWork
Жыл бұрын
Thank you! I'm so glad it helped! 😀
Jesus...I was so confused until I found this. Not the exact thing I was after, but gave me an approximate idea on what I should be doing. Crystal clear explanation. Thank you so, so much.
@ExcelatWork
4 жыл бұрын
I'm so glad it was helpful! Thank you so much for letting me know it helped you :-) Have a fantastic day. Sharyn
The best explained vlookup on KZread! Thank you so much 👍
@ExcelatWork
3 жыл бұрын
Thank you! So glad you found it helpful 😊
SO helpful! Spent hours trying to figure how to do this before I found this video and I was able to compare my two (very large) data sets in minutes!
@ExcelatWork
2 жыл бұрын
Great to hear! Thank you for letting me know. Love it!
I have saved many hours by your help .Thanks alot .👍👍
@ExcelatWork
2 жыл бұрын
Fantastic! So glad to hear that 😀
This as very helpful - Thank you
@ExcelatWork
Жыл бұрын
Thanks David! Yay! So great our video helped you. Thank you so much for letting us know 😊
You are fantastic. This was extremely helpful after spending hours trying to figure it out. I've signed up
@ExcelatWork
Жыл бұрын
Fantastic! So happy our video helped you!!
Very knowledgeable
@ExcelatWork
Жыл бұрын
Thanks
Thanks. I've learnt some good tips from this video, which I've already started using at work. Nice name for the channel. I like the pun.
@ExcelatWork
2 жыл бұрын
Awesome Cladius, thank you!
Fantastic teacher !
@ExcelatWork
2 ай бұрын
Wow, thank you!
Thank you I think I did it right !! You explained it so perfectly that I could understand subscribed to your channel for sure
@ExcelatWork
2 жыл бұрын
Awesome! Thank you!
very helpful thanks
@ExcelatWork
Жыл бұрын
Glad it helped
THANK YOU! I have struggled so much with this from other videos and gave up and decided to try again, this was SO easy to follow!! I was so excited when it worked for me! :)
@ExcelatWork
2 жыл бұрын
Yay! So glad it helped! Thank you so much for letting me know Liisa 😊
Perfect explanation and clarity . Thank you very much
@ExcelatWork
2 жыл бұрын
You are so welcome!
Thank you, thank you 😊 you saved a lot of time for me. You're amazing.
@ExcelatWork
3 жыл бұрын
You're very welcome!
You are amazing, this helped me so much after spending hours trying to figure out. I have subscribed 🙂
@ExcelatWork
2 жыл бұрын
Awesome, thank you!
Been looking to solve my challenge for the last hour and have been driven mad by the long list of clowns giving advice. Being under the cosh, I needed a solution fast...! Solved by your brilliant video, thanks Sharyn! Looking forward to watching your other video's.
@ExcelatWork
2 жыл бұрын
Fantastic! So glad I could help 😊
FANTASTIC. !
@ExcelatWork
2 жыл бұрын
Yayy! Glad you like it! Thanks Sheila 😊
Very much detailed and informative video.Great Work.
@ExcelatWork
3 жыл бұрын
Thank you so much for your kind comment Sadat. I'm so glad you found it helpful.
You are the best!!! You just saved my life lol
@ExcelatWork
2 жыл бұрын
Yayy! You are so welcome :-)
Informative! Thank you!
@ExcelatWork
3 жыл бұрын
You're welcome Jeffrey!
AMAZING - thank you!
@ExcelatWork
3 жыл бұрын
You're so welcome!
Thank you for great turtorial
@ExcelatWork
4 жыл бұрын
Thank you. I'm so glad it was helpful!
Need to redo this using XLOOKUP - don't need to move columns around and it's not going to break if someone inserts a column later
@ExcelatWork
Жыл бұрын
You're right Andrew. I need to do an additional video on XLOOKUP. Amazingly I'm still training people who don't yet have the XLOOKUP function so this video still has a few more miles left in her 😊
Do you do any classes for beginner excel users? This was super helpful
@ExcelatWork
2 жыл бұрын
Hi Kerry. Thank you and yes I do! I'm not sure where you are based but you can check out my courses here at excelatwork.co.nz 😀
Excelente video e informações. Obrigado
@ExcelatWork
4 жыл бұрын
Muito obrigado. Estou feliz por ter ajudado você. Eu usei o Google translate, então espero que você possa entender meu comentário :-)
@etrosul1
4 жыл бұрын
@@ExcelatWork Eu estou muito feliz por sua resposta. Estar em contato com você é motivo de grande felicidade. Muito obrigado e parabéns por seu excelente vídeo. Você é maravilhosa. Um grande abraço. Que Deus lhe abençoe, sempre.
T H A N K Y O U .. you saved my day...
@ExcelatWork
3 жыл бұрын
You are so welcome! I'm so glad it helped you 😀
Finally someone with a problem statement. Thanks a ton Sharyn. Please can I ask a few questions 1. We have now Index and Match and power query as well so why VLook Up 2. What are the other functions which are mostly useful with Vlook up ?again thank you. Are you from New Zealand ?
@ExcelatWork
3 жыл бұрын
Such a great question! Index and Match and Power Query are fantastic tools. However sadly not many people actually know about them. 9 times out of 10 I'm asked to teach people how to use VLOOKUP, mainly due to legacy files that are full of VLOOKUP formulas AND so many employers are still asking specifically for VLOOKUP skills when hiring. So I can't see VLOOKUP going away any time soon. Although I'm trying really hard to show as many people as I can the new XLOOKUP function (video coming soon) which is AMAZING! And yes! I'm from New Zealand. Kia ora my friend!! 😀
@ExcelatWork
3 жыл бұрын
Sorry, I didn’t answer your 2nd question. There are just so many. Definitely the TRIM function (video on this due out next week), the LEFT, RIGHT and MID functions and definitely the VALUE function. All fantastic functions to have in your toolbox to ensure your lookup functions work 👍
@granand
3 жыл бұрын
@@ExcelatWork Kia Ora legend
@granand
3 жыл бұрын
@@ExcelatWork True. .all used in Data remediation. Thanks a ton Legend ..look forward to see your videos & journey of knowledge with you ..
Hi, so it doesn’t have to be in ascending order? when you do vlookup.. thank you!
@ExcelatWork
3 жыл бұрын
You are very welcome. Thanks for your message!😀
I've followed your method accordingly. However, it did not show #N/A in my worksheet? Why is this happening?
@ExcelatWork
2 жыл бұрын
Hi Judy. It could be that the Error Checking option has been turned off. Please check this blog out excelribbon.tips.net/T010655_Turning_Off_Error_Checking.html I hope it helps.
I only have columns that have duplicates to work with, VLOOKUP is not working, what else can I use? 500 names in one, 141 in the other, I need to math which are in both. Thanks
@ExcelatWork
2 жыл бұрын
Hi Jo. You might like to give Conditional Formatting a go. Check out this blog trumpexcel.com/compare-two-columns/ The steps under the heading 'Example: Compare Two Columns and Highlight Matching Data' may be what you need. I hope it helps.
Please advise me. I am trying to do a check on just the figure. The invoice between the 2 worksheet is exactly the same. Only the amount is different. Will Vlookup show #N/A on the amount if invoice number is exactly the same for both worksheet??
@ExcelatWork
2 жыл бұрын
Hi Judy. As long as the VLOOKUP can find a match between the 2 worksheets it will return the figure. If you are trying to do a match based on the Invoice number AND the figure, you might like to check our my blog on how to do this excelatwork.co.nz/2021/08/22/vlookup-multiple-criteria-in-excel/
Question: I think me having duplicate values is what is causing my problems; however I need a way to return a value from sheet 1 based on a common item # in sheet 2. I do have multiple duplicate item #'s in sheet 2 and want to return a corresponding value from sheet 1 every time Excel find the numbers. What other function can I use if not VLOOKUP?
@ExcelatWork
3 жыл бұрын
Hi there. I've replied to your email. Please let me know if you have any further questions 😀
I have to compare two files at work, the vendor file has over 6,000 entries of item number, cost, regular price, sale price, description, etc. The items we carry at our store is only close to 700 items. I have to compare these two files and update any changes into our accounting system (lightspeed). I can export the files we carry from lightspeed into an excel file. and can download the vendor file into excel. So it sounds like I need to put both files into a work book, and try doing a vlookup based on the common factor of the item number. Seems I will need to sort each sheet by item number, lowest to highest to start. then start the comparison. Can you give me a direction from here how to complete the vlookup? Or is the the best method to do the comparisons? I have noticed other videos using IF formulas, and seems a bit confusing, since I don't know anything about these formulas either. I appreciate any direction you may have . Judy
@ExcelatWork
3 жыл бұрын
Hi Judy. Apologies for the slow reply. Sounds like you are on track. You don't need to sort the files if you are using VLOOKUP with a 'false' argument at the end of the function. If you follow this video and use the common factor of the item number for the lookup_value you should have success, e.g. the item number would replace the Invoice number example I use in the video. From what you have described to me I would recommend VLOOKUP instead of using IF functions. 😀
@judywest419
3 жыл бұрын
@@ExcelatWork thank you very for your reply. Will try it!
I followed every step and I'm still getting an error...
Though I appreciate your effort, not being able to explain 'absolute' with the F4 key or Fn+F4 on a Mac, you're kind of leaving out the point. This should be commented on vs... just saying to 'add in a $' with barely an explanation.