VLOOKUP() Function in Excel

Add to Favorites
Author: | Edits: don

Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you should use it.

The Vlookup function for Excel is a great function that allows you to search through a list of data within Excel.  This allows you to do things like use the ID number of an item to locate other data for that item without having to manually go through a list of data.

Though the function seems complex at first, it is quite easy to use once you become comfortable with the functions arguments.

Here is the function listed with all of its arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Below, each argument for this function is explained.

lookup_value

                This is the actual thing/item/value/etc. that you want to use to locate the desired data contained within your Excel spreadsheet.  This could be a unique ID number that attaches to a specific record; it could be a name; it could be a number that is contained within a range (explained in the range_lookup argument).  Simply put, this is the value that is used to locate the data that you want to return.  Once the Vlookup function finds this value, it will return data that is contained within the exact same row as where this value is found.  The col_index_num argument below is where you tell the Vlookup function from which column you would like to return the data.

                Note that this value is searched for within the left-most column of data that is contained within the reference stated in the next argument, table_array.  Also, you may use a cell reference for the lookup_value instead of an actual value; this is a common technique that is used so that you can return different data without having to change the actual Vlookup function.

table_array

                This argument tells the Vlookup function from which table of data you need to retrieve your values.  For this argument, you will select a range of cells, for instance: A1:D10.  In this example, the left most column is A and this is where the Vlookup function will try to find the lookup_value argument in order to return data from the corresponding row in which it is found.

                Make sure that you select a table_array that contains all of the data that you would like to return using the Vlookup function because you cannot return any data that is outside of this table array.

col_index_num

                This is where you tell the Vlookup function which column contains the data that you would like to return.  Following the example used in the table_array argument explanation above, if you want to return data from column C then you would put 3 for this argument.  The columns are numbered starting with 1 for the left-most column referenced in the table_array argument.

[range_lookup])

                This is the only optional argument and its value can only be either TRUE or FALSE.  This argument tells the Vlookup function if you want it to find an exact or approximate match.  If you input FALSE, which is the most commonly used value for this argument, Excel will only return an exact match where the lookup_value exactly equals a value found in the left-most column of the table stated in the table_array argument.  If this value is not found, it will return an error.  If multiple values are found, the Vlookup function will return the first value that it finds.

                If you leave the value for this argument empty, since it is an optional argument, it will default to TRUE, which you can also manually enter yourself.  In this case, the Vlookup function will return an exact match if one is found, BUT, if an exact match is not found, it will return the next largest value that is less than the lookup_value.  Also, in order for this to work, the first column of the table_array needs to be sorted in ascending order (the one that will be used to find the lookup_value).

This TRUE value for the range_lookup argument is most often used when you want to return something that can be within a range, such as school grades, where an A or a B is not just a single number but, instead, can be achieved by having a grade that is within a range of numbers such as 80-89 for a B and 90-100 for an A.

 

The Vlookup function is one of the most helpful functions that you will use within Excel.  Though it may seem scary and confusing at first, just stick with it and you will be able to input this function in seconds and save yourself hours of sifting through data.

 

 


Excel Function: VLOOKUP()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...
Understanding Formulas and Functions in Excel
Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...
Easily Input Complex Functions in Excel
Tutorial: In this tutorial I am going to show you how to easily input complex Functions in Excel. To...
Vlookup Partial Match in Excel
Tutorial: Return Vlookup results on partial matches of a cell's contents. You could type the start,...
How to use the CONVERT Function in Excel
Tutorial: Use the CONVERT function in Excel to switch between measurements, including distance, tem...
Create a Custom Function in Excel - UDF
Tutorial: How to create a custom worksheet function in Excel. These are called User Defined Function...
Tutorial Details
Excel Function: VLOOKUP()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course