The VLOOKUP is a very widely used function in Microsoft Excel used to search for a specific value in a table and return the corresponding value in another column of that table. In this blog post, we will discuss how to use the VLOOKUP function in Excel and how to use it to find information about a student with a roll number.
Primary VLOOKUP Function
The VLOOKUP function is used to search for a certain value in the leftmost column of a table and return the corresponding value from any other column in the same row.
The basic syntax of the Vlookup function is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Here lookup_value is the value you want to look up in the first column of the table. The table_array is the table that you want to search in, and col_index_num is the column number in the table from which you want to return the corresponding value.
The range_lookup is an optional argument that specifies whether you want an exact or approximate match. Excel will look for an approximate match if this argument is set to TRUE or omitted. If this argument is set to FALSE, Excel will look for an exact match.
Let’s want to find the information of a student with roll number 2. Here’s how you can do it using Vlookup:
- Select a cell where you want to display the result. Let’s say you want to find the first name of the student. For this, type the following formula: =VLOOKUP($J$4, $A$2:$D$13, 2, FALSE). (The $ sign is added to indicate an absolute reference).
- In this formula, we are using the J4 as the lookup_value since we want to find information about the student with roll number 2, which is in this cell.
- The table_array argument is set to A2:D13, which specifies the range of cells that contains the table we want to search in.
- The col_index_num argument is set to 2, which specifies that we want to return the corresponding value from the second column of the table (i.e., the Name column).
- Finally, the range_lookup argument is set to FALSE since we want an exact match. Press Enter to display the result.
The result will show the first name of the student with roll number 2. If you want to find other information, just change the col_index_num argument to the appropriate column number.
Exact vs Approx Match
The lookup function can be used with both exact and approximate match modes, depending on the type of data being searched.
The exact match is used when we have a specific value that we want to match exactly with the data in our table. In the example, we used the exact match (True) to find the discount percentage for a quantity of the products sold, and it returned the exact discount percentage of 10%.
The formula will =VLOOKUP($B2, $L$8:$M$13, 2, FALSE) for our example. It will return the percentage for exact values.
However, you will find that it returned an error for the different percentages.
That is because the exact match can’t return approx values. We can solve these errors using approx match. The approximate match mode finds the closest match to the lookup value in the table and returns the corresponding value.
To convert the above formula for an exact match to approx max, all we need to do is to change the last argument of FALSE to TRUE. This will return the approximate percentage for different quantities.
Wildcard
When using the VLOOKUP function, it is possible to perform a partial match on the lookup value by using wildcards. There are two types of wildcard operators that can be used: the asterisk (*) and the question mark (?). The asterisk can be used to find any text string after a certain character, while the question mark can be used to find any text string before a certain character.
To put this into practice, let’s say we will need to find a student name with the first few characters of their name. We can start by entering the lookup value and the table area into the VLOOKUP function. Then, add the wildcard operator (in this case, the asterisk) by using the ampersand operator (&) and enclosing the wildcard in quotes. Finally, close the parenthesis and press enter to see the result.
The formula for the wildcard match for our given example will be =VLOOKUP($J$2&”*”,B2:D13,1, FALSE).
This formula will return the first match (student name) for any text string in cell J2. The limitation of the wildcard match is that it will only return the first match.
Filter Function
If you want to return multiple matches when performing a lookup, then the filter function will be more useful for you.
For example, suppose you have a table of data with student names and the highest run by them. And there are multiple values for the same student. Let’s say you want to find the highest run values for Joshua.
The formula for the filter function is =FILTER(array, include,[if_empty]).
In our example, the array is A2:B15, include is A2:A15=”J3” (Joshua in J3 cell), and if_empty is “Not Found“. This will filter the table and return all the highest runs for Joshua in an array. The formula will be like this =FILTER(A2:B15,$A$2:$A$15=J3,”Not Found”)
It will return all the highest runs in an array.
Two Way Lookup (INDEX and MATCH)
Taking advantage of the two-way lookup feature can significantly amplify your ability to extract values from a two-dimensional table. All you need is an INDEX and MATCH combination in order to access data from any cell within your table.
Let’s learn it through an example. Here we have a sheet with sales data from different years. Now we want to look up the sales of 2021 for an SR named Joshua Kimmich.
We will use the formula = VLOOKUP($H$2,$A$2:$D$6,MATCH($H$3,A1:D1,0),FALSE).
This formula searches for the SR name in H2 (Joshua Kimmich) and then looks up the value of 2021 from the table using MATCH(H3,A1:D1,0). This will return the sales amount for Joshua Kimmich in 2021.
In this way, you can look up data from any cell in a two-dimensional table by using the INDEX and MATCH combination.
VLOOKUP Errors
When using VLOOKUP in Excel, errors can occur even if the formula is correct. These errors can be caused by missing values or criteria not being fulfilled. To address these errors, one can use helper functions such as IFERROR to trap errors and provide a default value. By wrapping the VLOOKUP formula in an IFERROR function, you can define a default value for when an error occurs.
Other functions like IFNA, ISERROR, and many others are also available in Excel to help address VLOOKUP errors. It is important to note that using an exact match or an approximate match (TRUE/FALSE) can also affect the outcome of the VLOOKUP formula.
Conclusion
VLOOKUP is a powerful Excel function that can be used to quickly lookup data from tables. With the help of wildcards, filter functions, and a two-way lookup feature, VLOOKUP can be used to make complex lookups within a table.
We have also discussed the ways to handle errors when using VLOOKUP in Excel. As long as you understand the different options available, you should be able to use this function to its full potential.
Good luck!
