Astha Sharma on 2021-07-27
We are going to compare how to combine two sheets using VLOOKUP, Index Matching, and Merge Spreadsheets.
If you are a visual learner, check out our YouTube video for this!
Let's say you have Sheet A that has your customer's Names & Emails.
And Sheet B that has your customer's Names & Addresses.
We want to combine these sheets so you have one file that has the customer's Names, Emails, & Addresses.
To use Vlookup, click on Formulas and Lookup & Reference
Next scroll to where it says VLOOKUP
Click on the cell you want to add the column to
In the Lookup_value we are going to enter the common value in both of our sheets. I highlighted the "Name" column which is A2:A47
Go to the other sheet and highlight the entire table. This will be the value of Table_array which is Email!A2:B44
For Col_Index_num enter the column you we are merging, in this case is 2
Enter "FALSE" for Range_lookup and press enter
All the emails will show up in this column! As you may notice, the order of both the sheets were different so it matched where it needed to.
Some values will say "#N/A" this means there is no match found between the sheets.
For Index Match you can do the same thing as before and access it through Formulas and then Lookup & Reference, or write it in the column we want our merging to go.
So we write =INDEX(Email!$B$2:B$44,MATCH(A2,Email!$A$2:$A$44,0))
The final result will look the same as before.
Click the Join button and upload your file(s)
Unlike Vlookup & Index Match, your data does not need to be in the same file to be merged together.
However, if you have one file with mutliple sheets, click on File Options and Import Sheets
Select the files and click on "Full Join" for Join Type and press the Join Files button
View & Download your merged file!
Merge Spreadsheets was the simplest way to merge the sheets together.
If you have any questions, feel free to email us at admin@lovespreadsheets.com
Watch our video for a visual comparison!
