The VLOOKUP Function (A Mystery to Many Excel Users)

by Meggen Mills

The VLOOKUP Function is one of the most useful functions in Excel.  It is also one of the most confusing and least understood functions.  This article describes the formula syntax and usage of the VLOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

Example:

You are a new employee in the Benefits department of a large company with over 2500 employees.

Suppose your department has just sent a notice to selected company employees (approximately 750 employees) notifying them that they still have  vacation days available this year, and your boss has asked you to determine the department these selected employees work in. The only problem is that the notice you sent did not ask for their department; you only know their Name and Employee ID number.

You are beginning to think that this might be a big job, looking up the department for over 750 employees!  Fortunately, you remember that your co-worker has an Excel workbook that contains a table with each Employee’s Name, Employee ID number and Department so you decide to use the VLookup feature you learned in your Excel class at Richland College to quickly identify each employee’s department on the vacation notices you sent.. Problem Solved!

And Here is How: 

First copy your co-workers data table including the Employees Name, Employee ID Number and Department into an Excel Workbook.  For illustration purposes, we will utilize a smaller sample of your coworker’s worksheet as shown in the data table below with the range A2:C10.

The employees’ ID numbers are stored in the first column of the range, as shown below:

VLOOKUP Data Table Example

VLOOKUP Data Table Example

 

 

 

 

 

 

Since you know the employee’s ID number, you can use the VLOOKUP function to return the department for each employee.

To obtain the department of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 2, FALSE).

This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the 2nd column of the range and on the same row as the lookup value (“Operations”).

VLOOKUP Explained

VLOOKUP Explained

The V in VLOOKUP stands for vertical. Use VLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

VLOOKUP demystified, hopefully!

_________________________________________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.