Table of Content
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.
Excel VLOOKUP Formula
The syntax of VLOOKUP in Excel consists of 4 arguments.
=vlookup(lookup_value, Lookup_table, column_index,[range_lookup]).
- Lookup_value: This is the value to be searched for in the column of the table.
- Lookup_table: The table from where to retrieve a lookup value.
- Column_index: This the column number in the table. This start from the column where to retrieve a value.
- Range_lookup: This argument contains 2 options. TRUE, FALSE.
- [TRUE]: This is an optional thing. This is an appropriate match. This is a default option in this argument.
- [FALSE]: This is the second option in this argument. This searches for an exact match in the column of the table.
VLOOKUP Examples for Basic And Advanced Users
Here are some examples of the VLOOKUP Function in Excel.
Excel Vlookup Examples #1
Step 1: To use 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 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 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: Next step is to give the lookup table correctly. The Lookup table is the one where 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 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.
Excel Vlookup Example #2
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 what 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 the confusions. Now close the braces. The vlookup example is ready to apply for now.
Excel Vlookup Examples #3
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 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 into this may be numeric data or the 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.
Drawback of VLOOKUP Function & Examples
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 about Vlookup Function & Examples
- When the range_lookup is removed, the VLOOKUP function will use as an exact match if exist, 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 main thing if the lookup value is not pointed correctly to the exact value.
- The function is not a 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 such a way that this uses data only from left side to right side. But in 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 –