Merging two Excel files using VLOOKUP function

danoh123 Jan 13, 2008 Computers
Merging two Excel files using VLOOKUP function is very powerful and convenient for Data Analysis when manual matching of two files is very time consuming because of size of data.

Merging two Excel files using VLOOKUP function is very powerful and convenient for Data Analysis when manual matching of two files is very time consuming because of size of data.

 

Let's look at one example situation here. 

Excel File One has two columns. Column one is for the student IDs and the other column is for dates ACT test were taken.

Excel File Two also has two columns. One column is for the student IDs and the other column is for dates SAT test were taken. 

Each file has several thousands of student IDs with test dates.  Since volume of data is very large, it will not be efficient to manually match student IDs between two files. The goal is to bring the SAT test dates from File Two to File one, third column.  In other word, we want to find out how many students took both ACT and SAT test together.

 

VLOOKUP is an excellent function tool to accomplish this task.

1. First, please open both files so that both file names show up on the bottom of screen.  Make sure the Student ID (which is the key of both file for matching) is in the leftmost column.

2. Display File One by pointing and click the file, and please position the cursor on the top cell of 3rd column in File One.

3. Choose VLOOKUP function after clicking 'fx' (Function), and click OK tab.

 

As soon as OK tab is clicked with function VLOOKUP being highlighted, another sub-window 'Function Arguments' will pop up.

4. In pop up window, please set four arguments as below:

Lookup_value: While bar-cursor is positioned in Look_value area, please move cursor to top first cell of column A of File One, which is for student ID.  Click the cell, and A1 will automatically show up in the Lookup_value area.

Table_array: Please move the cursor down to Table_array area and position to the left by simply clicking. While cursor is positioned in Table_array area of File One, please point and click File Two.  Now, File Two (SAT file) is displayed on the screen.  Copy the entire data area of File Two.  This Copy action will automatically fill up Table_array area with proper boundaries. 

Col_index_num:  Move cursor down to Col_index_mun area, and type in 2.  2 indicates the second column  of File Two which has dates of  SAT test.  SAT test date is what we really want to bring to File One.

Range_lookup: Please type in 'false', without quote.  And click OK.

5. As soon as OK is clicked, SAT test date in File Two will be brought into File One (third column, top cell) if student IDs in both files are the same each other.  When student IDs in File One and File Two are matching each other, SAT dates will be successfully brought into.  If not matching, #N/A will be populated.  So far, the formula has been generated and copied into the first cell of File One. 

 

6. The formula needs to be copied to the rest of cells of 3rd column, File One.  Once Copy is completed, all SAT test date will be automatically brought into from File Two, for matching Student IDs. 

7. Please remember that the SAT dates that are shown in 3rd column are calculated dates, not the real/permanent values.  We need to make these dates as permanent: Highlight the column, COPY, EDIT/PASTE Special, and choose Value within Paste Special sub-window.

 

Please practice these steps several times.  Author has been using this tool many times for Data Analysis between different files.

What did you think of this tutorial?
+ 34
14 CommentsAdd a Comment
Sharon on Apr 23, 2014
 
I followed the directions - perfect! What a time saver!
hasan5 on Mar 27, 2014
 
thanks but picture in this totorial is not clear
uratwat on Dec 13, 2013
 
this is so badly written, i think a blind deaf monkey could have written better english
Binita on Oct 2, 2013
 
Thanks for the tutorial...It was very helpful
David Deenson on Mar 16, 2012
 
It actually wound up being easier to save the Excel files as 2 .CSV files, and then write a C# application to merge them into 1 new CSV file, and then open that up in Excel.
David Deenson on Mar 16, 2012
 
Would be more useful if the images were large enough to be viewed.
Pietro on Dec 16, 2011
 
Cheers - helped me save loads of time.
Adithya on May 30, 2011
 
This is one of the few awesome blogs!!
Patty on Feb 19, 2009
 
This was very helpful and allowed me to follow along using my own documents that I wanted to merge and it worked!!!
JoeBenson on Dec 10, 2008
 
THis is lovely....may god bless the wonderful person who posted this..
meow on Jan 13, 2014
 
HA GAAAAAAAAAAAAAAAAAAAY
chikkanna on Oct 27, 2008
 
Thanks for your tutorial
RUTENDO MAMUTSE on Oct 27, 2008
 
WOW, i am supposed to go for a second interview and my intervier gave me an assignment on V-LOOKUP and i should say i am very much impressed. thank you so much, will give you feed back after i come back from my interview.
JEGADESH GOPAL on Oct 17, 2008
 
ITS WORKING I REALLY ENJOYED AND STUDIED THE VLOOKUP
Name
Comment
  • Views : 22584
  • Comments : 14
  • Rating : + 34
  • Last Updated : Jan 13, 2008