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.
= 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)
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
= 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:
The marks of Henry can be obtained using VLOOKUP formula are as follows:
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:
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.
By now, I think the concept of the first 3 parameters of this function is clear to you 😊
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:
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:
Look the following screenshot to understand the approximate match using VLOOKUP more clearly:
Or you can use the following formula:
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.
The following screenshot will explain the functioning:
= 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 😊
VLOOKUP function will give you the desired result if keep the following things in mind: