Using the VLookup Function in Excel 2007
Summary: Learn how to dynamically search table arrays in Microsoft Office Excel 2007 by using the built-in VLOOKUP function.
Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Visual Basic for Applications (VBA)
Michael LaRosa, Murphy & Associates
In this example, you create three calculated columns using the VLOOKUP function in a variety of ways. It uses information about students at a hypothetical state university.
Open and Examine StudentInfo
In this example you will work with the file StudentInfo.xlsx, which you can download from Code Gallery. Open this file in Office Excel 2007. It contains a workbook with three worksheets:
Students contains key information about registered students. This is the worksheet that you will be modifying.
GPA lists the students' quarterly and averaged yearly grade point average, on a scale from 0 to 4.0.
Grades contains a GPA-to-letter conversion table. (It also contains some UI at the bottom that can be used in a procedure for calculating a grade from a GPA value. This is discussed in the section Using VLOOKUP from VBA.)
This workbook represents a sample of an actual workbook that could potentially contain information about thousands or tens of thousands of students.
Adding a Buddy Email Column
The university pairs all incoming freshman with one another in a "buddy" system. Currently in the Students worksheet, the Class Buddy column lists the ID of the corresponding student. Although this information is available via a manual search in this same table, to enable easier access, another column with the buddy's email will be added. To accomplish this, you will use the VLOOKUP function to search in the same table using an exact match.
To create the buddy email column
Adding a Column to Confirm Buddy Compatibility
Buddies are usually required to be in the same graduation class. To help monitor this proscription, a new column is requested to keep track of this relationship. Again you will use VLOOKUP to search in the same table using an exact match, but the results will then be used in a further calculation.
To create the paired class validation column
Adding a Grade Column
Lastly, a request has been received to extend this worksheet with the current student average grade. To accomplish this, you perform a nested lookup using tables on the other worksheets.
To create the grade column
The Resulting Students Worksheet
After you have added these three new columns, the resulting Students worksheet should appear as in Figure 1 (except that in the figure, columns D-G have been hidden to conserve space).
The built-in VLOOKUP function in Office Excel 2007 is used to dynamically search for matching values in a table arrays. Given a value, it performs a vertical search in the first column of the specified table, and if it finds a match, it returns a value from the specified column in the same row.
This function can be used to find information in a single worksheet or, more commonly, to use a known value in one worksheet to search for associated data in a row of another, related worksheet.
Office Excel 2007 contains a number of related search functions, including:
Searches for an exact matching value and, if successful, returns an associated value. The LOOKUP function has two syntax forms: the vector form and the array form.
This horizontal lookup function is the counterpart to VLOOKUP. It searches for a value in the first row of a table array, and if a match is found, then it returns a value from the specified row in this same column.
Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
VLOOKUP has the following syntax:
lookup_value is the value to search for in the first column of the table array. This parameter can hold a value or a reference.
Exact text searches can use the question mark (?) and asterisk (*) wildcard characters to match a single character or any sequence of characters, respectively.
Exact matches on floating point values may not succeed because of rounding approximations. Instead, perform an approximate match or use the TRUNC function.
The following conditions will result in error values being returned:
If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns #N/A.
If col_index_num is less than 1, then #VALUE! is returned; if greater than the number of columns in table_array, then #REF! is returned.
If a match is not found, #N/A is returned.
Using VLOOKUP from VBA
The VLOOKUP function is exposed in the Office Excel 2007 object model as the WorksheetFunction.VLookup method. For example, the following code could be added to the Grades worksheet to perform a basic GPA-to-grade conversion. This code utilizes the existing UI elements already found in this worksheet: the button named GradeCalc, and named cells GpaInput and GradeOutput.
Note: VBA code will only function in macro-enabled spreadsheets and templates. If you want to add and test this code, save the companion file StudentInfo.xlsx as a macro-enabled spreadsheet named StudentInfo.xlsm.
' Event handler to calculate a grade from a GPA Sub GradeCalc_Click() Dim oGradeCell As Range Set oGradeCell = Range("GradeOutput") oGradeCell.Value = "" Dim res As Variant Dim errNum As Integer 'Traps and reports all application errors On Error Resume Next res = Application.WorksheetFunction.VLookup(Range("GpaInput"), _ Range("A2", "B12"), 2, True) errNum = Err.Number If errNum <> 0 Then res = "Error: " & errNum End If oGradeCell.Value = res End Sub
This code must be implemented as an event procedure for the GradeCalc button.
To create an event procedure for an existing form control
In addition to the standard runtime errors, such as out of memory, described in Core Visual Basic Language Errors, the VLOOKUP method also issues the application-defined error (error number 1004) for the error conditions described in the previous section.