by Simple Mind

Created on: December 31, 2008   Last Updated: April 08, 2011

How to Use the VLOOKUP Function in Excel

For those of us who work with large amounts of data, efficiency and accuracy are paramount when manipulating information among various spreadsheets or workbooks. To that end, Excel contains a number of functions to expedite what would otherwise be a mind-numbing task. In particular, I've found the "VLOOKUP" function to be invaluable because it allows you to search a range of data based on your parameters and returns the data you specify. This is helpful if you are working with multiple worksheets or files in Excel sharing a common identifying ID such as an order number, inventory SKU number or the name of an individual.

Let's say, for example, that you are building a spreadsheet of 50 students in a particular class with their corresponding book report grade assigned to the column cell immediately to the left of their names (so if the name of the student is in cell "A2", then their grade would be placed in "B2"). A separate worksheet within the same workbook (or perhaps a completely independent Excel file) exists containing the scores of assignments and quizzes of all 300 of your students. Rather than having to search through all the extraneous data manually, the "VLOOKUP" function will perform this task for you with minimal preparation. As input, it simply requires the value being searched, the range we are searching within, and what column we wish to extract the data from.

The function itself is set up in the following manner:

=VLOOKUP(lookup_value, table_array, col_index_num,
range_lookup)

A real example with actual data plugged in would look like:

=vlookup(a1, c1:g35, 2, false)

The following explains what each variable represents:

"Lookup Value"- the information, data or value that is being searched such as a name or inventory ID number.
In our student grade example, it would be the student name.

NOTE: The VLOOKUP function will ALWAYS search the first column of the range you selected for the "Lookup Value".

"Table Array" The range or area we are searching within. "A1:B3" would denote an area including cells A1 through A3 and B1 through B3. I find it much easier to simply name the ranges by first selecting the cells in question and then typing in an appropriate name representing the range in the Name Box" (the box in the tool bar displaying the cell address you last clicked on). Let's say you are attempting to search a range from which you want to retrieve a particular homework assignment score for each student. I would

• 1
• 2
• 3
• Next Page >>