How to use VLOOKUP function in Excel?

The VLOOKUP function has wide utility and is one of the most important functions in Excel. This function has many aspects that I will try to explain in simple language for the better understanding of the beginners in Excel 😊

The VLOOKUP (V stands for vertical) function looks for the data in the specified column and returns the output from different column whose value  corresponds to the lookup value.

The syntax and function of its parameters:

​= VLOOKUP (Lookup_value, Table_array, Column_index_number, [Range_lookup])

​As you can see that this function has 4 parameters, generally 3 parameters are required and the 4th one is optional. Actually, the 4th parameter has its importance that I will explain to you as we proceed further in this tutorial.

• Lookup_value is the value which you want to look in the column. It can be any number, text, date or a cell reference.
  

For example:

= VLOOKUP(35,A1:B21,2) (It will look for number 35)

= VLOOKUP(“Dorothy”,A1:B21,2) (It will look for the name “Dorothy”. Remember to put the text in the double inverted commas)

= VLOOKUP(C1,A1:B21,2) (It will look for the value entered in the cell C1)

• Table_array

It is the range of data which contains the lookup_value and the return value. The lookup_value should always be in the first column of the specified range.

For example, if your range is B2:C21, then the lookup_value must exist in column B2 and you should count it as column number 1.

The values can be text, date or the numbers and these values are case insensitive that is the lower case and the upper case is treated as identical.

The formula VLOOKUP(35,A1:B21,2) will look for number 35 in the column A1 to A21 as A is the first column of the table_array

• Col_index_num is that column which contains the return. Again, refer to the same formula

= VLOOKUP (35,A1:B21,2)

This formula will look for number 35 from the column A1 to A21 and returns the value from column B which is in the same row of number 35.

Let us better understand it with the help of an example 😊

Mrs. Hilton is planning to hold the parent-teacher meeting to discuss the performance of the students in the test held last month.

There are 20 students in her class and she wants to facilitate the meeting by avoiding searching the name of the student from the list and telling her/his performance every time when the parents of the concerned student come and meet her.

She is planning to find a way that whenever she enters the name of the students in her desktop, her/his performance get displayed on the screen.
​
The VLOOKUP function can be used here to save her time and labor.
The list of the students along with their marks are as follows:

List of students

​The marks of Henry can be obtained using VLOOKUP formula are as follows:

Finding marks of Henry using VLOOKUP function

She can further make this process easy by writing the lookup value in another cell and using VLOOKUP formula by referring the cell number.

Please see the following screenshot:

Using VLOOKUP function by referring the cell number

Retrieving Henry’s marks from the different worksheet:
​
Generally, we are required to retrieve information from another worksheet. In that case, Mrs. Hilton can use the following formula to get the desired result.

Extracting data from different worksheet using the VLOOKUP function

By now, I think the concept of the first 3 parameters of this function is clear to you 😊

• The 4th parameter Range_lookup is optional but is very important and can give different results when you write True or False in this place. It does two functions of finding the exact match when its value is set to False and of finding the approximate match when its value is set to True or omitted.

​VLOOKUP function with Exact Match 

When the 4th parameter is set to False, then the formula searches for the value which is exactly same as the lookup_value.

And if there are two or more values similar to the lookup_value, then the formula will choose the 1st same value found and return the answer. 

​Please see the following screenshot:

Using VLOOKUP function for exact match

• In case the exact match is not found then it returns #NA

​VLOOKUP with approximate match:

If the range_lookup value is set to True or omitted then the formula will look for the closest match.

Actually, first, it searches for the exact match with the lookup_value and if in case, it doesn’t find the exact match then it looks for the closest value less than the lookup_value.

In order to get the right results, arrange the first column that is the Marks column in the ascending order otherwise the approximate match will give erratic results. 

​The following list is sorted in ascending (from smallest to the largest) order:

Arranging the first column in ascending order

Look the following screenshot to understand the approximate match using VLOOKUP more clearly:

Using VLOOKUP function for approximate match

Or you can use the following formula:

= VLOOKUP(60%,A1:B21,2,True)

I hope the above screenshot clears the functioning of VLOOKUP with an approximate match 😊

​Using wildcard characters “?” and “*” with VLOOKUP

There are times when we do not remember the exact data but can recollect the part of it. We can retrieve the complete data by combining part of data with the wildcard characters.

• “?” is used to match any single character
• “*” is used to match the string of characters

​
The following screenshot will explain the functioning:

Using VLOOKUP function with wild card characters

= VLOOKUP(“Dor*”,A1:B21,2,False) finds the name starts with “Dor”. It is “Dorothy” and the output is 31%

= VLOOKUP(“????????”,A1:B21,2,False) will find the name which has 8 characters and returns the value corresponding to it.

The name with 8 characters is “Angelina” and the value corresponding to her is 76% (Output)

Note: For the wildcard characters to work properly, always set the last parameter to False, otherwise it will not function and will return #NA. If more than one values meet the set criteria then the first found value will be returned as the output by the formula.
​
If I am not wrong then by now you have become quite familiar with this function 😊

Conclusion:

   VLOOKUP function will give you the desired result if keep the following things in mind:

• The VLOOKUP function cannot look at its left and it always searches for the values to its right. Therefore, always start with the leftmost column in Excel
• When searching with approximate match either set the 4th parameter as TRUE or omit it.
• When you are searching with the exact match then set the 4th parameter as False
• To avoid erratic results, always sort the first column of the table_array in the ascending order while searching with the approximate match (the 4th parameter is set the True or omitted)
• Always use False as the last parameter while using the wildcard characters
• If you specify range as D2:F21 then count D as the first column, C as the second column and so on