Microsoft Power BI Certification Training Cou ...
- 26k Enrolled Learners
- Live Class
In this data-driven world, one needs various tools in order to manage data. Data in real-time is huge and fetching details regarding some particular piece of data would definitely be a tiring task but with VLOOKUP in Excel, this task can be achieved with a single line of command. In this article, you will be learning about one of the important Excel functions i.e the VLOOKUP Function.
In Excel, VLOOKUP is a built-in function that is used to lookup and fetch specific data from an excel sheet. V stands for Vertical and in order to use the VLOOKUP function in Excel, the data must be arranged vertically. This function comes in very handy when you have a huge amount of data and would be practically impossible to manually search for some specific data.
The VLOOKUP function takes a value i.e the lookup value and starts to search for it in the leftmost column. When the first occurrence of the lookup value is found, it starts to move right in that row and returns a value from the column that you specify. This function can be used to return both exact as well as approximate matches (The default match is an approximate match).
The syntax of this function is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
When you want the VLOOKUP function to look for an exact match of the lookup value, you will have to set the range_lookup value to FALSE. Take a look at the following example which is a table consisting of employee details:
The VLOOKUP function starts to look for the employee ID 104 and then moves towards the right in the row where the value is found. It goes on till the col_index_num and returns the value present at that position.
This feature of the VLOOKUP function allows you to retrieve values even when you do not have an exact match for the loopup_value. As mentioned earlier, in order to make VLOOKUP look for an approximate match, you will need to set range_lookup value to TRUE. Take a look at the following example where the marks are mapped along with their grades and the class to which they belong.
In a table that is sorted in the ascending order, VLOOKUP starts to look for an approximate match and stops at the next largest value that is smaller than the lookup value that you have entered. It then moves right in that row and returns the value from the specified column. In the above example, the lookup value is 55 and the next largest lookup value in the first column is 40. Therefore, the output is Second Class.
In case you have a table that consists of multiple lookup values, VLOOKUP stops at the first match of it and retrieves a value from that row in the specified column.
Take a look at the image below:
The ID 105 is repeated and when the lookup value is specified as 105, VLOOKUP has returned the value from the row that has the first occurrence of the lookup value.
VLOOKUP function is case insensitive. In case you have a lookup value that in upper case and the value present in the table is small, VLOOKUP will still fetch the value from the row in which the value is present. Take a look at the image below:
As you can see, the value that I have specified as a parameter is “RAFA” whereas the value present in the table is “Rafa” but VLOOKUP has still returned the specified value. If you have an exact match even with the case, VLOOKUP will still return the first match of the lookup value irrespective of the case used. Take a look at the image below:
It is natural to encounter errors whenever we make use of functions. Similarly, you can encounter errors when using VLOOKUP function as well and some of the common errors are:
This error basically is to inform you that you have made some mistake in the syntax. To avoid syntactical errors, it’s better to use the Function Wizard provided by Excel for every function. The Function Wizard helps you with information regarding every parameter and the type of values that you need to enter. Take a look at the image below:
As you can see, the Function Wizard informs you to enter any type of value in place of the lookup_value parameter and also gives a brief description of the same. Similarly, when you select the other parameters, you will see information regarding them as well.
This error is returned in case no match is found for the given lookup value. For example, if I enter “AFA” instead of “RAFA”, I will get #N/A error.
Two-way lookup refers to fetching a value from a two-dimensional table from any cell of the referenced table. In order to perform a two-way lookup using VLOOKUP, you will need to use the MATCH function along with it.
The syntax of MATCH is as follows:
MATCH(lookup_value, lookup_array, match_type)
Instead of using hardcoded values with VLOOKUP, you can make it dynamic bypassing in the cell references. Consider the following example:
As you can see in the image above, the VLOOKUP function takes in the cell reference as F6 for the lookup value and the column index value is determined by the MATCH function. When you make changes to any of these values, the output also will change accordingly. Take a look at the image below where I have changed the value present in F6 from Chris to Leo and the output also has been updated accordingly:
In case I change the value of G5, or both F6 and G5, this formula will work accordingly displaying the corresponding results.
You can also create drop-down lists to make the task of changing the values very handy. In the above example, this should be done to F6 and G5. Here is how you can create drop-down lists:
In case you do not know the exact lookup value but only a part of it, you can make use of wildcards. In Excel, the “*” symbol represents a wildcard character. This symbol informs Excel that the sequence that comes before, after or between must be searched and there can be any number of characters before or after them. For example, in the table that I have created, if enter “erg” along with wild cards on either side, VLOOKUP will return the output for “Sergio” as shown below:
In case you have multiple lookup tables, you can use the IF function along with it in order to look into either of the tables based on some given condition. For example, if there is a table holding data of two supermarkets and you need to find out the profit made by each of them based on the sales, you can do as follows:
Create the main table as follows:
Then create the two tables from which the profit has to be fetched.
Once this is done, create a Named range for each of the newly created tables. To create a named range, follow the steps given below:
Once this is done for both the tables, you can use these named ranges in the IF function as follows:
As you can see, VLOOKUP has returned the appropriate values to fill the Profit column according to which supermarket they belong to. Instead of writing the formula in each cell of the Profit column, I have just copied the formula in order to save time and energy.
This brings us to the end of this article on VLOOKUP in Excel. I hope you are clear with all that has been shared with you. Make sure you practice as much as possible and revert your experience.
Got a question for us? Please mention it in the comments section of this “VLOOKUP in Excel” blog and we will get back to you as soon as possible.
To get in-depth knowledge on any trending technologies along with its various applications, you can enroll for live Edureka Online training with 24/7 support and lifetime access.