Excel VLOOKUP Function
MS Excel, or Microsoft Excel, is powerful spreadsheet software with a distinctive range of built-in functions and formulas. Each function has a specific purpose and application. VLOOKUP is an extremely useful Excel function primarily used while working with advanced formulas within large data sets across sheets. Using the VLOOKUP function seems a little difficult for beginners and often leads to some unexpected errors in Excel. However, to become a master in Excel, we must learn the Excel VLOOKUP Function.
What is the VLOOKUP function in Excel?
In Excel, VLOOKUP stands for 'Vertical Lookup', meaning the function only works vertically for the organized or structured table to be searched for the desired value. It is a built-in Excel function that searches for a specific value in the desired column to retrieve the respective value from a different column but on the same row. In simple words, the VLOOKUP function enables us to search for any specific piece of information within our worksheet while working with the large data sets.
For example, the VLOOKUP function tells Excel to look for the desired piece of information (i.e., RAM) in the given data set or a range (i.e., table) and return some other respective information about that (i.e., the price of RAM).
The VLOOKUP function is available in Excel 2007, 2010, 2013, 2016, and other higher versions.
VLOOKUP Formula in Excel
To enter a VLOOKUP formula in the desired cell (s), we need to type =VLOOKUP and select the function from the list by pressing the TAB key on the keyboard. After that, we can supply the respective arguments based on the syntax of the VLOOKUP.
Syntax of VLOOKUP
The syntax of the VLOOKUP function is defined as below:
Where, -lookup_value," -table_array," -col_index_num," and -range_lookup" are the arguments of VLOOKUP function.
Arguments of VLOOKUP
The VLOOKUP function requires the following arguments in which the first three arguments are mandatory while the last argument (range_lookup) is optional:
It is a required argument to specify the value we need to search for in the first column of the supplied table array or range. A lookup value can be in the form of any value like text, number, and date or the value obtained by any other function in the sheet. Unlike the numeric values or cell references, we must always enclose the text values within the straight double-quotes.
It is another required argument used to specify the data array or a range of cells where the function will search for the lookup value. The function always looks up in the table array's leftmost column (first column) and retrieves a corresponding match. The table array may contain multiple numeric values, text values, dates, and/ or logical values.
Like the above two arguments, the col_index_num is also a required argument. It is specified as an integer to represent the number of a specific column from which we want to obtain the desired value. It must be selected from the supplied table array or a range only.
It is an optional argument of the VLOOKUP function used to define what the function must return when it does not find an exact match to the lookup table. We can specify this argument as either TRUE or FALSE.
How to use the VLOOKUP function in Excel?
After understanding the basics of the VLOOKUP function, using it in an Excel sheet becomes a little easier. The functionality of VLOOKUP is the same in all versions of Excel and even in other spreadsheet programs. To use the VLOOKUP function in our sheet, we need to perform the following steps:
Step 1: Organize the Worksheet Data and Type Formula Name.
We must organize or structure our sheet data properly. Since VLOOKUP only works in a left to right order, we must ensure that the value we want to look up is present in the left side column of the data we want to obtain.
Once the data is properly structured, we must select the resultant cell to record the retrieved value, type "=VLOOKUP(" without quotes, and follow the next step. It is recommended to select the VLOOKUP function from the function list to avoid misspelling the function name.
Step 2: Tell the Function What to lookup or search for.
After entering the function name and starting parenthesis, we need to select a cell or type the respective cell reference where the information we want to look up is recorded.
Step 3: Tell the Function where to look.
In the next step, we need to supply or enter the range or table where our effective data or value is present. By entering the table, we tell Excel to search in the leftmost column of this given data and find the information we want to look up.
Step 4: Tell Excel which column should be used to get the result.
After supplying the effective data range or table, we must specify the column number to tell Excel to extract the data we want as an output via VLOOKUP. The column number is written or specified in a general numeric form. For example, if our output data is present in the 2nd column of the supplied table/range, we must specify the column number like 2 in the formula.
Step 5: Tell Excel whether to find an Exact or Approximate Match.
Lastly, we must tell Excel whether we want to lookup for the exact match or approximate match. We must enter TRUE (or 1) for an approximate match and FALSE (or 0) for the exact match. For example, when we want to lookup for any ID or word, it is better to use an exact match. In contrast, the approximate match is useful when looking for an exact figure that may not typically be present in the table.
Let us understand the working of the VLOOKUP function better with the help of examples:
Let us discuss examples to look for values in exact and approximate matches using VLOOKUP:
Example 1: Exact Match in VLOOKUP
Consider the following excel sheet as an example where we have data containing a list of some items with their prices. We need to use VLOOKUP to find the price of any specific item (i.e., 'Mouse') from the table.
We must perform the following steps to find the price of Mouse from our data table:
In our example sheet, we can change the item name in cell F2 and get the corresponding price for that item in real-time.
Example 2: Approximate Match in VLOOKUP
Consider the following excel sheet as an example where we have recorded data containing a list of scores with their respective grades. We need to use VLOOKUP to look up the value 75 and find the corresponding grade from the table.
Since our table does not contain the value 75 in the leftmost column, we use VLOOKUP to return an approximate match. We must perform the following steps to find the nearest match (grade) for our lookup value (75) from our data table:
We can type other random scores (or numbers) to get their respective grades. If an exact match is found, the function will return its respective grade accordingly.
Limitations of VLOOKUP
Although VLOOKUP is a powerful function, it has certain limitations, i.e.:
VLOOKUP always looks right
One of the major limitations of the VLOOKUP function is that it cannot return the value or output from any of the left side columns of the lookup value. The function always works by looking up the value in the leftmost column of the selected table while returning the respective value from the desired column to its right.
For instance, consider the following sheet as an example data set where we have some employees' names with their salaries.
We can use the VLOOKUP function to find the salary of any desired employee in the following way:
However, we cannot find the employee's name based on his salary. The VLOOKUP function cannot use the salary as a lookup value while returning the value from its left side column. Instead, it will return an error.
To use Left Lookup, we can use the combination of INDEX and MATCH functions in Excel.
VLOOKUP is Case-insensitive
The VLOOKUP function in Excel works in a case-insensitive way. This means the function treats uppercase and lowercase values equally when looking through the table. For instance, we will still get the results if we try to lookup an employee name in the UPPERCASE (i.e., BIPIN).
Since VLOOKUP is case-sensitive, it will work the same for lookup values BIPIN, Bipin, bipin, bipiN, etc.
VLOOKUP always finds the first match
The VLOOKUP function always produces a result for the first lookup value it finds vertically in a table column. If there are duplicates in the column, all such values will be ignored. For example, the VLOOKUP function returns the salary for the employee name 'Bipin' from cell B4, not from B10. It is because the function always matches the first instance and is case-insensitive.
Excel VLOOKUP Errors
When using VLOOKUP in Excel, we often encounter unexpected errors instead of the desired results. Some such errors include the #N/A, #NAME?, #REF!, and #VALUE!.
Important Points to Remember