How to compare 2 big sheets with vlookup? | Excel 2010
How to compare 2 big sheets with vlookup?
We have 2 big sheets with data and we want to compare them. Our first sheet includes all the products we have in inventory as at August 31 and the second sheet contains inventory status as at September 30.
To compare 2 values, we first need to get them to same sheet. As sheets might have different products we cant just copy from one to another. Thats why we are going to use the vlookup function.
First things first, please make sure the sheet we're looking the information from is sorted. Now, coming back to the sheet we're going to compare the data later, we select the cell we want the values to show up and write equals and Vlookup into it. Inside brackets we first define what value we are searching, which is the product name. Then we define where we are searching for comparing value. For this we go to second sheet and choose first 2 columns. We choose 2 columns because we need to include product amount, our end result, also. Then we define in which of the columns the value is. In our case it's the second one so we type in 2. Finally we define if we want the exact match and for that write FALSE.
Now that we have entered the formula, we can quickly apply this to all the rows by double clicking on the corner. Note this only works if columns next to it don't have any blank values. It will stop as it finds the first blank cell.
As you can see some columns give us #N/A -- it means this product name is not found on the second sheet. If you didnt set the last parameter FALSE then it would have found the closest match to missing product name, which is not what we want.
And now you can start comparing the data.
http://www.officetodo.com
