VLOOKUP() Function in Excel

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.