How to compare and match two files using Excel VLOOKUP?

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.