Excel VLOOKUP Function

MS Excel, or Microsoft Excel, is termed to be powerful spreadsheet software with a distinctive range of built-in functions as well as formulas. And each function in Microsoft Excel has a specific purpose and application. VLOOKUP is an extremely useful Excel function that is primarily used while working with advanced formulas within large data sets across the sheets as well. Making use of the VLOOKUP function seems a little difficult for beginners and often leads to some unexpected errors in Microsoft Excel. However, to become masters in Microsoft Excel, we must learn the Excel VLOOKUP Function respectively.

What is the VLOOKUP function in Microsoft Excel?

In Microsoft Excel, VLOOKUP primarily stands for the 'Vertical Lookup', meaning the function only works vertically for the organized or structured table to be searched for the desired value as well. 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 large sets of data respectively.

• For example, The VLOOKUP function usually tells Microsoft Excel to look for the specific piece of information i.e., RAM (Random Access Memory) in the given set of data or a range that is none other than the table and it will return some other respective information about that (i.e., the price of RAM) respectively.

The VLOOKUP function is now available in:

1. Microsoft Excel 2007.
2. Microsoft Excel 2010.
3. Microsoft Excel 2013.
4. Microsoft Excel 2016, and other higher versions as well.

What is VLOOKUP Formula in Microsoft Excel?

Now to enter a VLOOKUP formula in the desired cell (s), we need to type =VLOOKUP and then we need to select the function from the list by just pressing the TAB key on the keyboard. After that, we can easily supply the respective arguments based on the syntax of the VLOOKUP as well.

Syntax of VLOOKUP function in Microsoft Excel

The syntax of the VLOOKUP function is defined below:

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

In which,

• lookup_value," -table_array," -col_index_num," and -range_lookup" are the arguments of VLOOKUP function.

Arguments of the VLOOKUP in Microsoft Excel

The VLOOKUP function in Microsoft Excel usually requires the following arguments which the first three arguments are mandatory while the last argument (range_lookup) is optional respectively:

1. Lookup_value
   It is a required argument that can be used to specify the value we need to search for in the first column of the supplied table array or range as well. 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" respectively.
2. Table_array
   It is the other required argument that can be used to specify the data array or a range of the 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 effectively.
3. Col_index_num
   Like the above two arguments, the col_index_num is also a required argument. It is specified as an integer used to represent the number of a specific column from which we want to obtain the desired value as well. It must be selected from the supplied table array or a range only.
4. Range_lookup
   It is an optional argument of the VLOOKUP function that can be used to define what the function must return when it does not find an exact match to the lookup table. We can also specify this argument as either TRUE or FALSE.
   
   • TRUE: When the argument is set as TRUE, the function tries to find out the approximate match if no exact match is found. The function then finds the closest match below the lookup_table. We can also make use of '1' to specify the argument as TRUE.
   • FALSE: The function only tries to find out the exact match when the argument is set as FALSE. The function returns an error if no exact match is found below the lookup_table. We can also make use of '0' to specify the argument as FALSE.

How to make use of the VLOOKUP function in Microsoft Excel?

After understanding the basics of the VLOOKUP function, using it in an Excel sheet becomes a little easier. The functionality of the "VLOOKUP" is the same in all versions of Microsoft Excel and even in other spreadsheet programs. And in to make use of the VLOOKUP function in our sheet, we need to perform the following steps efficiently:

Step 1: Organize the Worksheet Data and Type Formula Name.

We just need to organize or structure our sheet data properly. Since VLOOKUP only works in left-to-right order, we just need to ensure that the value which we want to look up is present in the left side column of the data we want to obtain as well, and once the data is properly structured, we just need to select out 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 look up or search for.

After entering the function name as well as the starting parenthesis, we are required to select a cell or type of the respective cell reference where the information we want to look up is recorded respectively.

Step 3: Tell the Function where to look.

And in the next step, we need to supply or enter the range or the table where our effective data or value is present. By just entering the table, we tell Microsoft Excel to search in the leftmost column of this given data and find the information which 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 the table, we just need to specify the column number to tell Microsoft Excel to extract the data which 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 or range, we just need to specify the column number like 2 in the formula as well.

Step 5: Tell Excel whether to find an Exact or Approximate Match.

And lastly, we must tell Microsoft Excel whether we want to lookup for the exact match or an approximate match. And for this, we just need to 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 make use of 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 as well.

Let us now understand the working of the VLOOKUP functions much better with the help of the following examples:

Examples

Let us now discuss the below examples to look for the values in exact and approximate matches by making use of the VLOOKUP:

Example 1: Exact Match in VLOOKUP

Let us now consider the following Excel sheet as an example where we have data containing a list of some of the items with their prices as well. And we need to make use of the VLOOKUP to find the price of any specific item (i.e., 'Mouse') from the table respectively.

And to achieve this, we just need to perform the following steps to find the price of the Mouse from our data table:

Step 1: First of all, we need to select a cell to record the price of the Mouse. Now in our selected example sheet and this example we have selected sheet cell as G2 as well.

Step 2: And after selecting a resultant cell, we just need to start typing the VLOOKUP function starting with an equal sign and select from the function list by just pressing the TAB key on the keyboard respectively.

Step 3: Nextly, we need to select the cell with a value that we want to look up. We select or type the cell F2 in the "VLOOKUP function" as the first argument in our example respectively.

Step 4: And just after that we need to supply out the first argument, and we type Comma (,) and select the range (or table array) in which we want to search for the lookup value and obtain the desired output. In our case, we just need to select the range (A2:D12) without brackets as well.

Step 5: Now in this step, just after entering out the range, we again need to type a Comma (,) to separate the arguments efficiently. After that, we are required to type the column number within the supplied range in which we want to look up the output value as well, but in our case, it is the price column, so we need to select type 4.

Step 6: And lastly, we will be then entering the last argument as FALSE or 0 to find an exact match for the lookup value 'Mouse', just after that, we will type the ending parentheses and press the Enter key from our keyboard to obtain output respectively.

And in our respective example sheet, we can easily change the item name in the cell that is none other than F2 and get the corresponding price for that item in real-time respectively.

Example 2: Approximate Match in VLOOKUP

Let us now consider the following Microsoft Excel sheet as an example where we have recorded data containing a list of the scores with their respective grades, and we need to make use of the VLOOKUP to look up the value 75 and find the corresponding grade from the table respectively.

Since our table does not contain the value 75 in the leftmost column, so, in that case, we can easily make use of the VLOOKUP to return an approximate match respectively. And we must need to perform the below-following steps very carefully so to find out the nearest match (grade) for our lookup value (75) from our data table:

Step 1: First of all, we just need to sort the leftmost column of our data range in ascending order. It is essential while making use of the VLOOKUP function in an approximate match mode. After that, we are required to select a cell to record a grade efficiently, as in our example sheet; we need to select a cell F4.

Step 2: Now in the next step, we are required to enter the VLOOKUP formulas which are similar to the previous example as well. We will first type the "=VLOOKUP (" without quotes, and then we will select the cell with a lookup value (F3, in our case), select the data table (B2:C9, in our case), and type the column number (2, in our case) from which we want to retrieve the output respectively.

Step 3: And unlike the previous example, we will now enter TRUE as a fourth argument. It primarily tells the VLOOKUP function to return an approximate match when no exact match is found. But in our case, the function does not find the lookup value 75 in the first column of the supplied range, so it returns the largest value smaller than 75. In our example sheet, the approximate match value is 70. Therefore, just after pressing the Enter key on the keyboard, the VLOOKUP function returns the output grade C, which is the grade for score 70.

Moreover, we can easily type other random scores (or numbers) to get their respective grades. If an exact match is found, then the function will return its respective grade accordingly.

What are the various limitations of the VLOOKUP in Microsoft Excel?

It was well known that the respective VLOOKUP is a very much powerful function, but besides all this, it has certain limitations which are as follows:

VLOOKUP always looks right

One of the major limitations regarding the VLOOKUP function is that it cannot return the value or output from any of the left-side columns of the lookup value. And the function always works by just 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, let us now consider the following sheet as an example data set where we have some 'employees' names with their salaries respectively.

And we can easily make use of the VLOOKUP function to find out the salary of any desired employee in the following way as well:

However, we cannot easily find out the employee's names that are based on his salary. And the VLOOKUP function cannot make use of the salary as a lookup value while returning the value from its left-side column. Instead, it will return an error.

And to make use of the Left Lookup, we can easily make use of the combination of INDEX and MATCH functions in Microsoft Excel.

VLOOKUP is Case-insensitive

The VLOOKUP function in Microsoft Excel works in a case-insensitive manner, which means that the function treats uppercase as well as lowercase values equally when looking throughout the table. And for instance, we will be still getting the results if we try to look up an "employee name" in the UPPERCASE (i.e., BIPIN) respectively.

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 get ignored.

• For example, It was well known that the VLOOKUP function returns the salary for the employee name 'Bipin' from cell B4, not from cell B10. It is because of the reason that the function always matches the first instance and is case-insensitive effectively.

Excel VLOOKUP Errors

While making use of the respective VLOOKUP in Microsoft Excel, we often encounter unexpected errors instead of the desired results. Some such errors include the following:

1. #N/A.
2. #NAME?.
3. #REF!.
4. #VALUE!

• VLOOKUP #N/A Error: It usually occurs when the lookup value is misspelled or it is not present in the selected table as well, the lookup column is not the leftmost column, hidden spaces in lookup values, and/ or numerical values are formatted as texts respectively.
• VLOOKUP #NAME? Error: It occurs when the function name is misspelled or wrong.
• VLOOKUP #REF! Error: It occurs when the column number (col_index_num) is higher than the range selected.
• VLOOKUP #VALUE! Error: It mainly occurs when the lookup value contains more than 255 characters, missing parameters or arguments, and/ or column number (col_index_num) is below 1 respectively.

What are the Important Points which need to be remembered while working with the Vlookup function in Microsoft Excel?

The various important points that need to be remembered by an individual while working with the "Vlookup Function" in Microsoft Excel are as follows:

1. If in case an individual does not specify the range-lookup in VLOOKUP, the function returns an exact match if it exists; otherwise, non-exact (approximate) match respectively.
2. If in case the sheet already has the VLOOKUP formula, then it is recommended not to insert a new column or delete an existing column in the table range as well. When an individual inserts or delete a column in the selected table, the column index number does not change accordingly, resulting in incorrect results or an error.
3. VLOOKUP can look up values with signs or with symbols.

• For example, an asterisk (*), question mark (?), etc.