Скачать или смотреть Excel - Compare 2 Lists with a VLOOKUP - Episode 1534

Microsoft Excel Tutorial: Compare old and new versions of Excel data using VLOOKUP

Welcome to VLOOKUP Week here at MrExcel Podcast! In this episode, we will be tackling a classic problem - comparing two lists. This is a common task when working with data and there are always three questions that need to be answered: What did the manager delete? What did the manager add? And what values did the manager change? We will be using VLOOKUP to find the answers to these questions.

First, we start with our original data and send it to our manager. After working on the data, we now need to compare the two lists. To find out what was deleted, we use the VLOOKUP function to look for the code in the original list. If the code is not found, it means it has been deleted by the manager. We can easily identify these items by the N/A result in the VLOOKUP formula.

Next, we want to see what the manager added to the list. We use the same process as before, but this time we add a column and use the VLOOKUP function to look for the code in the original list. Again, any items with an N/A result must have been added by the manager. We can then use sorting to bring these items to the top of the list for easier identification.

Now, for the items that are in both lists, we want to see if the manager changed any values. We insert a new column and use the VLOOKUP function to look for the code in the original list. We then use conditional formatting to highlight any cells where the value has changed. This allows us to easily see which items have been changed and we can use sorting to bring them to the top of the list.

And there you have it - using VLOOKUP to compare two lists and find out what has been deleted, added, and changed by the manager. Thank you for watching and be sure to tune in for more helpful tips and tricks from MrExcel. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents:
(00:00) Compare 2 Lists with VLOOKUP
(00:24) Three Questions to Answer
(00:34) Finding Deleted Items
(01:10) Identifying Added Items
(01:53) Checking for Changed Values
(03:02) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Compare 2 Lists in Excel
Conditional formatting in Excel
Learn Excel from MrExcel Podcast
Red font formatting
Sorting data in Excel
Using VLOOKUP to analyze data
VLOOKUP classic problem
VLOOKUP function
VLOOKUP Week
What did the manager add?
What did the manager delete?
What values did the manager change?

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...


In the spirit of "VLOOKUP WEEK 2012" [march 25th - March 31st, 2012], Bill centers on VLookup again today. In theory, I sent my workbook to my manager. I have received the workbook back from my manager and now I need compare the data [the original data with the new data] to find out what was deleted, what was added and what was changed. Today Bill shows us how to work with VLOOKUP to find the answers to our questions.

Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel. Check out the VLOOKUP WEEK 2012 Blog!