One of the Excel functions that I use often to "combine" data from different tables is VLOOKUP. This function especially comes in handy when I am working with large amounts of student data in different tables from different sources.

Although I speak of VLOOKUP as "combining" data from two or more tables, Excel actually uses a lookup value that you identify, such as ID, to find a match from the leftmost column of a second table. It then returns data from a column that you specify in the second table.

In other words, Excel uses the ID field common to both tables and searches for the data that you need vertically (hence VLOOKUP) across the rows or student records of the second table array.  When it finds a match in the second table, it takes the information from the column that you want to import and places that information where you want in the first table.

ID numbers are useful as a lookup value for student data because they are unique to each student.

Here is an illustrated example of how VLOOKUP works:

The Writing Department has a spreadsheet of students in its program and wants to ascertain each student's advisor name and the number of credits he or she is taking, as shown below:

This information is available on a spreadsheet from the Advising office that contains a list of all students of all majors, as shown below:

First, make sure the Lookup value (ID) is on the leftmost column on both lists, and sort by ID on both lists in ascending order. (Data/Sort/Ascending).

VLOOKUP Syntax/Formula:
=VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value found in the first column of the array. Lookup_value can be a value, a reference or a text string.

• In our example, the lookup value is a cell containing the student ID.  Our lookup value will be the first cell containing an ID number.

Table_array is the table from which you want data looked up and imported. 

• In our case, the table array is the entire cell range of the table containing all students from the Advising office.

Col_index_num refers to the column number in the table array from which the value is imported. If you want data from the third column of the table array, the col_index_num is 3.

• The column number for the Advisor column is 5; the column number for the Credits column is 6 on the table of all students from the Advising office.

Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match of the lookup value. If Range_lookup is TRUE or left blank, an approximate match is returned. An approximate match refers to the next largest value that is smaller than the lookup value, If Range lookup is FALSE, VLOOKUP will find an exact match of the lookup value. If one is not found, the error value #N/A is returned.

• Since we are looking up ID numbers, we need to specify FALSE for the range_lookup since we want an exact match for an ID.

Back to our example:\

We have two tables: (1) the smaller "Writing Department" table and (2) the larger "All Majors" table. Again, make sure that ID is on the leftmost column on both tables and sort both lists by ID in ascending order.

On the Writing Department table, click on the first cell where you want data from the "All Majors" table placed. This should correlate with the first student you are looking up and can be the column on the far right.

1. Click fx on the formula bar.
    
2. In the Search for a function box, type VLOOKUP. In the Select a function box, VLOOKUP is highlighted. Click OK
   
   
    
3. The Function Arguments window pops up.
    
4. In Lookup_value, (cell containing value to lookup, i.e. ID. ) click on the the first ID on your table, or in this example, it's the cell A3.
    
5. In Table_array (name of table containing data to look up) Click on the Expand/Collapse dialog box icon.
   
   Locate and click on the Excel spreadsheet "All Majors." Select the entire cell range of the table array that contains data. 
    
6. The Function Arguments box and the formula bar displays the table path of the lookup table like this:
   
   
   
   
    
7. Click on the Exapand/Collapse dialog box icon again to close it.
    
8. In Col_index_num (column number containing data to be imported), type 5. The Advisor Column was the fifth column in the All Majors table.
    
9. In Range_lookup, type FALSE.  We want an absolute match for each ID. The complete Function Arguments looks like this:
   
   
    
10. You should now see Advisor names appear in the Writing Department table. The advisor for the first record is Christina King.  Use Fill Down (CTRL + D) to copy this formula down the column to get Advisor names for each student.
     
11. You can repeat the VLOOKUP function for the Credits column, or copy the VLOOKUP function to the Credits column.  If you copy the formula, be sure to check that the LOOKUP VALUE (ID) is A3 and the COL_INDEX_NUM is 6 for the 6th column.
    
    The final result of VLOOKUP is shown here.
    Note: The student Bianca Hunchinson returned a value of N/A because her ID and hence record was not on the "All Majors" list.
     

If you have any questions or need further help with VLOOKUP, please contact Fong Tan.