Excel VLOOKUP FunctionMS 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.
 The VLOOKUP function is now available in:
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,
 Arguments of the VLOOKUP in Microsoft ExcelThe 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:
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.
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 VLOOKUPLet 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 rightOne 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-insensitiveThe 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 matchThe 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.
 Excel VLOOKUP ErrorsWhile 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:
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:
Next TopicWhat is Pivot Table |
We provides tutorials and interview questions of all technology like java tutorial, android, java frameworks
G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India
Home
Python
Java
JavaScript
HTML
SQL
PHP
C#
C++
DS
Aptitude
Reasoning
Selenium
DBMS
C
Andriod
Interview Q
