While working with data, we have to deal with huge data sheets containing plenty of information. Having to look for a piece of particular information in this sea of data may seem to be a daunting task.
As a teacher, I often faced this problem. When I had to search for the score of a particular student, it would mean going through hundreds of names. Pretty time-consuming, isn’t it?
Thanks to the VLOOKUP formula of Excel, it is easy to find particular information within seconds. All you have to do is write the syntax, and you will get your desired result.
In an age where almost all industries rely on data-driven decisions, data analytics, and its different tools play an important role. MS Excel is a traditional software that has facilitated easy data management since its inception. It has functions that can help you get your work done in absolutely no time!
In this tutorial, I will discuss how to use VLOOKUP in Excel, its benefits, and how it is different from the LOOKUP function. Happy reading!
About the VLOOKUP Function in Excel
VLOOKUP is the shortened form of “vertical lookup”. It is a built-in feature in Excel that is used to work with data that is organized into columns. The function helps to find or “look up” values in one data column and return the corresponding value from the adjacent column.
What differentiates VLOOKUP from the LOOKUP function is that the former helps in conducting a vertical search, whereas the LOOKUP function helps in searching for values both horizontally and vertically.
The VLOOKUP formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), where lookup_value represents the value that you are looking for, table_array represents the data range where you want to search, and col_index_num is the column from where you want to extract the result.
The [range_lookup] part is optional. It is set to FALSE for an exact match and returns an error message if the value is not found.
You can also use the formula to search for data across sheets. The formula that you need to use for this is quite similar to the regular VLOOKUP formula. The only difference is that you will have to mention the sheet name in the table_array argument in order to instruct the formula in which worksheet the lookup_range is located.
The formula for VLOOKUP with different sheets is:
=VLOOKUP(lookup_value, Sheet ! range, col_index_num, [range_lookup])
This function can be best understood with an example.
An Example to Understand the Use of VLOOKUP
Let us understand this with an easy step by step easy VLOOKUP formula use case.
For instance, let us take a dataset that has the marks of 10 students in four subjects.
Source: MS Excel
We will use the Excel VLOOKUP function to find the marks secured by CDE in Maths. Since this is a small dataset, the information is clearly visible. However, finding such information manually is not easy for larger data sets.
However, you can easily find the information using the VLOOKUP formula. Choose any blank cell where you want the data to be displayed.
Step 1: Start the formula with an equals sign followed by VLOOKUP. Open the brackets where you will have to add the arguments.
Step 2: Next, you will have to add the arguments. These arguments will instruct the VLOOKUP syntax on what to look for and where to search for it. Therefore, in this case, the first argument will be CDE. It is important to put CDE in double quotes because it is a text.
=VLOOKUP(“CDE”
Step 3: Next, we will have to mark the cell range from where the data can be extracted. In our case, the range will be B34:C43.
=VLOOKUP(“CDE”, B34:C43
All arguments must be separated by a comma. This syntax will instruct Excel to look through the first column first and then return the value present to the right of it.
Step 4: Now, we will have to put the third argument that will denote the column index number. The first column in the range becomes column 1, the second column in the range becomes column 2, and so on.
Here, we are trying to find the marks scored by CDE in Maths, which are in the second column. Therefore, we have to put the third argument as 2.
=VLOOKUP(“CDE”, B34:C43, 2
Step 5: In the next step, we have to write TRUE or FALSE. TRUE helps us look for an approximate match, whereas FALSE helps us find the exact match.
In this case, since the value we are looking for is a numerical value, we have to type FALSE.
=VLOOKUP(“CDE”, B34:C43, 2, FALSE)
Close the parentheses and press Enter.
Source: MS Excel
I hope this example gives a clear idea of how to search for specific values using the VLOOKUP function in Excel.
Looking for approximate values using the VLOOKUP Formula
Sometimes, you have to look for approximate values in a dataset. The VLOOKUP function in Excel helps to find such values by identifying the value that is lesser than the value mentioned in the VLOOKUP function in Excel.
Let us take this example, which mentions the price of six devices and their RAM specifications. I will provide a step-by-step guide on how to look for data that are not rounded off.
Source: MS Excel
Step 1: First, you need to specify the lookup value. Let the lookup value, in this case, be 1470.
Step 2: You will have to arrange the data in ascending order using the Filter option.
Step 3: Enter the VLOOKUP function in Excel. The syntax remains the same, that is, =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
In this case, you will have to write =VLOOKUP(1470, A54:B59, 2, TRUE).
The syntax will deliver the result 8, which is adjacent to the value 1465.35, which is the value lower than and nearest to 1470.
Source: MS Excel
Now that you have a fair idea about how to use the VLOOKUP formula in Excel with examples let’s take a look at some common mistakes that we often make when using the formula.
Common Mistakes to Avoid While Using the VLOOKUP Formula
Sometimes, when we make a mistake in the VLOOKUP formula, the function shows an error message. Here are some errors that you may encounter while using the VLOOKUP function in Excel:
- #N/A! error: This message is shown when the VLOOKUP function does not find a match to the lookup_value mentioned in the syntax.
- #REF! error: If the col_index_num argument is greater than the number of columns in the given table-array, then the formula will show an error message.
- #VALUE! error: This message is shown if the col_index_num is less than 1 or is not a numeric value, or if the range_lookup argument is not identified as either TRUE or FALSE.
Limitations of Using the VLOOKUP Function in Excel
Although the VLOOKUP formula is highly beneficial, there are some limitations that we need to be careful about. They are as listed below:
- The VLOOKUP feature is not case-sensitive. Therefore, it is likely that the formula will return incorrect results if the case of the lookup value is different from that of the data in the lookup range.
- The formula only identifies matches in the first column of the lookup range, and you cannot use it to look for a value in a different column.
- This formula does not accurately return the results if the lookup value occurs more than once in the range. It will only show the first instance.
- VLOOKUP can only manage data that is sorted in ascending order.
Wrapping Up
I hope this tutorial gave you an idea of the function of VLOOKUP, how to use it, and some of the common mistakes that you need to be aware of while using it.
There are many such functions like the in Excel that make it easier to work with huge datasets. You can enroll in a course that will not only help you upskill, but will also provide you with a certificate that will validate your knowledge of the subject.
upGrad offers many such courses, specially designed for working professionals who want to attain new skills and seize new opportunities in the professional realm. Therefore, find the course that you like the best, and sign in today!
Frequently Asked Questions
1. How do I use the VLOOKUP on Excel?
Using the VLOOKUP function on Excel is pretty simple. You have to use the syntax =VLOOKUP(lookup_value, Sheet ! range, col_index_num, [range_lookup]).
2. What are the 3 rules for VLOOKUP?
The 3 rules for VLOOKUP are as follows:
- The data in table_array has to be arranged in a column.
- If search_value is a text, you have to put it in quotes.
- It is important to remember that the VLOOKUP formula can look for values from left to right.
3. How do I do a VLOOKUP between two sheets in Excel?
To conduct a VLOOKUP search between two sheets, use the syntax: =VLOOKUP(lookup_value, Sheet ! range, col_index_num, [range_lookup]).
4. What is the LOOKUP function in Excel?
The LOOKUP function in Excel helps to search for values vertically as well as horizontally.
5. Why is VLOOKUP useful?
The VLOOKUP function helps to save a lot of time and effort by searching for and retrieving data in a table. The data, however, has to be arranged vertically.
7. What is the basic LOOKUP function?
The basic LOOKUP function helps to search for values both horizontally and vertically, i.e., across the sheets.
8. What is lookup table with example?
In MS Excel, a lookup table is a range of cells from which we try to derive a lookup value. For example, if we have a dataset containing 12 product IDs and their respective sales values, we can perform LOOKUP or VLOOKUP functions on it. Hence, it is called a lookup table.
9. What is the difference between VLOOKUP and lookup?
Both lookup and VLOOKUP formulae are used to search for a value. However, in lookup, the search is conducted across the sheet, both horizontally and vertically, while in VLOOKUP, it only looks for data arranged vertically.
