I looked everywhere for articles that teach you how to compare ALL fields (not one or two like in most articles) between two spreadsheets and did not manage to find one. This post would be beneficial to compare worksheets that have thousands of data. So I did some research and here I am, doing this because I know how it feels to look for answers everywhere but to no avail. However, this is just the steps on how to compare using vlookup, not how to use vlookup 😊
Here’s an example of two spreadsheets that I want to compare. My objective of this comparison is to check whether Sheet1 has the same data in Sheet2 although lists are not arranged in the same way.
• Notice how in Sheet1 Mary is at line 3 but Sheet2 Mary is at line 8.
• And in Sheet1 Ad likes Yellow but in Sheet2 Ad like Orange.
Spreadsheet 1 is named ‘Sheet1’ while spreadsheet 2 is named ‘Sheet2’.
‘Sheet1’
‘Sheet2’
Step 1
Add a new field on the right and combine the data on all columns on the left in Sheet1.
=A1&B2&C2
It will turn out like this.
Do the same for Sheet2.
Step 2
In Sheet1, find the formula button and search for vlookup.
Enter the fields as below.
D:D in my formula means the whole D column. Read more on the vlookup function.
It will look something like this.
When you press enter, it will find the data in Sheet2 and shows ‘N/A/ if the data is not available.
Step 3
Now, to make it look easier for analyzing, we can add the match function.
It will turn out like this.
So from this, you can analyse the differences between two sheets. Let’s say you have thousands of lines, you won’t have time to compare one by one. Thank you!
