Google Sheets VLOOKUP function can be used to look for a value in a column and when that value is found, return a value from the same row from a specified column.
Now if that description sounds nerdy and difficult, here is another way to understand what this function does.
Suppose you have gone to an expensive restaurant and you are looking at their menu to order something nice (but you also don’t want it to be heavy on the pocket).
So you start scanning the menu and when you find something you like, you move your eyes to the right to see the price of that dish.
That’s exactly how Google Sheets VLOOKUP function works.
If goes down the column of lists/items, looks for the specified item and then returns its corresponding value from the same row.
If you’re still confused about the Google Sheets VLOOKUP function, just bear with me till we reach the examples section.
But before that, let’s quickly have a look at the syntax of Google Sheets Vlookup Function:
Syntax of Google Sheets VLOOKUP Function
VLOOKUP(search_key, range, index, [is_sorted])
- search_key – This is the value or item you’re looking for. For example, in the case of the restaurant, it would be burger or pizza.
- range – this is the range to be used in the Vlookup function. The left-most column of this range would be searched for the search_key.
- index – this is the column number from where you want to get the result. The first column in the range is 1, the second column is 2, and so on. Note that this value should be between 1 and the total number of columns. If not, then it would return a #VALUE! Error.
- is_sorted – [TRUE by default] – in this argument, you can specify whether you’re looking for an exact match or an approximate match. You can use FALSE for exact match and TRUE for an approximate match. When you use TRUE, the list needs to be sorted in an ascending If you don’t specify a value here, it takes TRUE as default. Note that for using an
Now let’s look at some examples to understand how to use Google Sheets Vlookup function in real life scenarios.
Example 1: Find Student’s Marks From the List
In the Example below, I have the names of students and their score in a subject (let’s say Math).
As a teacher, you may need to quickly pull the marks of students from a list that could be huge.
Knowing VLOOKUP would be helpful in such cases.
Here is the formula that will give you the marks of the specified students.
Now when you change the name in cell E2, the formula would automatically update and return the marks of that student.
Pro Tip: You can also create a drop down list of students so you don't have to enter the name manually.
Example 2: Find Student’s Grade using Google Sheets VLOOKUP Function
In Example 1, you looked for an exact match of the name to fetch the marks.
In this example, let me show you how to use the approximate match to get the grades of students based on their marks.
Below is the grade table that would determine the grade of a student:
In this example, we need to find the grades in column C based on the marks (in column B). The grading scale is in E2:F7.
Now before using this, you need to know that the grading range needs to be in the given format. For example, you can not have 0-33, 33-50, 50-70, and so on here. You need to have the numbers that are sorted in an ascending order.
Here is the formula that will give you the grade:
How this works: The VLOOKUP Function looks for the specified score (which is the search_key in this case) and looks for it the ‘Marks Range' column (which is the left-most column of the lookup range). It goes from top to bottom and when it finds the number which is greater than itself, it returns the grade from the previous row. For example, if the grade is 44, then the function would look through the numbers in E2:E7. Since 0 is less than 44, it moves to 33 which is again lower than 44, so it moves to 50 which is higher. So it goes back to the previous value (which is 33) and returns its grade (which is E).
Example 3: Two Way Lookup Using Google Sheets Vlookup Function
What we have seen so far is using Vlookup to return the value from a single column, since we had hard coded the value. For example, in the case of Example 1, it would always return the score from column 2, as we had hard coded the value 2 in the formula.
However, suppose you have a data set as shown below:
Here, you can use the 2-way lookup technique to fetch the marks for Brad (in cell F4) in Math (in cell G3).
Here is the formula that will do this:
How this works: In this case, to make the subject part dynamic, we have used the MATCH function within the VLOOKUP function. MATCH function looks for the subject name in A1:D1 and returns the column number where it finds the match. This column number is then used in the VLOOKUP function to return the marks of the specified student in that subject.
Related: A detailed guide on using the Excel Vlookup Function.
You May Also Like the Following Google Sheets tutorials: