VLOOKUP Function Examples in Excel
VLOOKUP is the most widely used function in Excel. VLOOKUP stands for Vertical Lookup in Excel. “V” stands for Vertical. This function searches for the required value vertically in a column in a table. This will be done for an exact match or for an appropriate match. Here are some examples of the VLOOKUP Function in Excel.
The syntax of VLOOKUP in Excel consists of 4 arguments.
VLOOKUP Examples for Basic And Advanced Users
- Step 1: To use the VLOOKUP function in our excel example, the data is to be organized to its good extent so that it is easy to use the function. This can be illustrated in the below screenshot.
The data is organized in such a way that when we are using VLOOKUP it should be in left to the right format.
VLOOKUP function works from left to right manner generally. So, this is the clue that the data is to be organized in a left manner only when extracting the data.
- Step 2: Place the function where there is a requirement after organizing the data.
The highlighted cell is the cell where I would like to enter my formula because I would like to find the Price associated with the given dress.
- Step 3: Place the data correctly to lookup the data in the function of what to lookup exactly by giving all the arguments correctly in the cell where the formula is present.
Now we will see the arguments one by one given in the function.
- Step 4: the first argument is the lookup value. This is the main argument in the function. What to look in the table or what to search in the table.
- Step 5: The next step is to give the lookup table correctly. The Lookup table in excel is the one where the lookup value is to be searched in the column.
- Step 6: Now the Column index is given; the column index is the argument where the index number of the column we are looking for is given.
- Step 7: This is the last step in the process. We need to give Boolean character true or false. Here true is for the appropriate match and False is for the exact match. In the appropriate match, the Data will be matched as per the arguments appropriately i.e.., if the data contains more than one word in it then only first word will be looked up. For an exact match, all the words in the data are matched and then only one field will be given after the exact match case is done.
The last argument is also passed correctly. Now the VLOOKUP example is done. The result will be displayed.
Now we drag the formula and results shown below.
In the below Excel VLOOKUP example, we are applying VLOOKUP function for B2 cell, to get the marks of related Sid’s. After the data is organized, the First Step is to select the cell where to apply the formula as shown below.
I wanted to find out the marks of the SID numbers given. Hence, I would place the formula in the other table and in the Marks Column as shown above.
- Step 2: Is to write the correct formula. For this type “=vlookup “in the cell where you need to get the result. Then the formula will auto-populate and press tab now. The formula will be entered there and now we need to type the arguments now.
- Step 3: Now apply the formula with correct arguments. The first argument is the lookup _value. What is the value we are looking for? This would be as shown in the below screenshot.
- Step 4: Now press tab and the next argument would be selecting the Table_Array. This would be the source table where we can find the SID in it. This would be as shown in below screenshot.
- Step 5: The next argument would be the column index. This is the serial number of the column that we are looking for from the Lookup value column in the source table. Here Lookup value is “SID” and “what we need is “Marks”. Hence the serial number of “Marks” Column from “SID” would be 4 considering SID serial number as “1”. This is illustrated in the below screenshot.
Step 6: This would be the last and a general step for every scenario. That means, there are only two options for this argument for every case. Either true or false. As we discussed “TRUE” indicates the appropriate match. This is indicating by the below screenshot.
Generally, the “Exact Match” option is taken to avoid confusion. Now close the braces. The vlookup example is ready to apply for now.
In the below Excel VLOOKUP example, as we have discussed below the VLOOKUP can only look at the values from left to right, in this case, that can be illustrated.
The explanation for this would be, we are looking to find the “Sno” for the given “Gender” Value. This is not possible because the “Sno” value is the right side to the “Gender” Column. This case is not possible in the VLOOKUP. This can be considered as a drawback for the “VLOOKUP” formula also.
Hence it is throwing an error called NA. That indicates that “A value is not available in the formula or Function”. Hence VLOOKUP can only be used from left to right.
Excel VLOOKUP Errors
After the VLOOKUP is done there might be the possibility of getting the error also if the formula is not applied correctly or if the data is not taken correctly. There are three types of errors in VLOOKUP.
- #N/A! Error
- #REF! Error
- #VALUE! Error
Now we will look at what are the cases where these errors would occur,
- #N/A! Error: This error occurs if the function will not find an exact match to the given lookup value.
- #REF! Error: This error occurs if the Column_index number argument given is greater than the number of columns in the given table.
- #VALUE Error: This error occurs if the column_index is not given a number, or the second case is if the range_lookup is not recognized as a Boolean value 0 or 1 or logical value true or false.
Relevance and Uses VLOOKUP
The VLOOKUP function is used when there is a need to find the exact match or appropriate match. The data will be used in this may be numeric data or text data. The usage of the VLOOKUP examples is very easy. Place the cursor in the place where you want to apply the formula, now select the arguments lookup value, lookup table, column index, range lookup as per the formula, and now close the braces and press Enter Key. The formula will be applied, and the result will be displayed in the given cell.
This can be used from only columns Left to Right only. The Vice Versa is not possible for this function (Also, see VLOOKUP to the Left)
Things to Remember
- When the range_lookup is removed, the VLOOKUP function will use as an exact match if it exists, but generally, it uses a non-exact match.
- We need to remember that which type of error will occur at what time and what would be the scenario for the error to occur whether the range_lookup is not given correctly or column_index argument is not passed correctly or the main thing if the lookup value is not pointed correctly to the exact value.
- The function is not case sensitive.
- We cannot use user-defined arguments in the function.
- For using the VLOOKUP function there should not be duplicate values. If the duplicates are present, then the function will match only the first value.
- The biggest limitation of this function is that this function will always use the data only to the right. That means the function is defined in such a way that this uses data only from the left side to the right side. But vice versa, this cannot be applied.
- VLOOKUP function is most widely used in financial calculations.
This has been a guide to Vlookup Examples in Excel. Here we discuss examples of Vlookup function for basic and advanced users along with downloadable excel template. You may learn more about excel from the following articles –