**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 MAT**CH(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!