How to use the VLOOKUP Google Sheets function

The VLOOKUP Google Sheets function is perhaps one of the most widely used functions in Google Sheets. It stands for Vertical Lookup. This searches for a key value in the first column of the input range, and it returns the value of a specified cell from the row where it finds the key. You can expect an error if the key doesn’t exist.

Syntax

• search_key – is the value that the VLOOKUP function uses to search.
• range – is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first column of the range.
• index – this is the column index of the cell within the range, whose value the function returns. The first column’s index within the range is 1, the second in the range is 2 and so on. For example, if we input 3 against this parameter, the VLOOKUP function returns the cell value from the third column and the row in which it finds the search_key.
• [is_sorted] – is an optional parameter which is TRUE by default. This indicates whether the first column in the range is sorted in ascending order or not. If not, we should specify the value as FALSE.

How to use VLOOKUP function

Numbers in the first column

Let’s try out on a few examples. Here is a test data set with sales figures for a group of salespeople. I will try to answer various business case scenarios (column D), using VLOOKUP. You’ll observe how the index parameter value affects the outcome.

I’ll choose first example here to dissect and ensure that you understand the basic use of the function. The formula =VLOOKUP(11876,A2:C11,2,FALSE) is used to tell the function to search for the value 11,876 within the range of cells from A2 to C11. Once it finds the value, it is instructed to return the data in the second column of the row it found the data in. The False indicates that the data is not sorted, and that you want an exact match to the search key.

Here’s an interesting thing to note in the last case (row # 7 above). The function returned an #N/A error. Why? That is, as explained in the error description, because the $15000 sales figure does not exist in the first row. Since I specified that I want an exact match (by indicating False for the is_sorted parameter), it cannot return an appropriate value.

The curious case of [is_sorted]

In the examples, I have chosen FALSE for the last parameter, which tells the function to find an exact match with the search key. This is typically how the VLOOKUP function is used. But what happens when you use True instead to find an approximate match?

Here, there are two examples that were use VLOOKUP to find the person with $12,000 of sales. The first function uses an is_sorted parameter of False, which tells the function to find an exact match. Because there is no salesperson with exactly $12,000 of sales, the function returns an error.

The second example is identical except it uses True instead, which tells the function that the data is sorted and it should find an approximate match. The function then tries to find the sales number that is closest to $12,000, and returns the name of the corresponding salesperson – Finch.

Notice that the salesperson closest to $12,000 sales is actually Gary. The VLOOKUP function finds an approximate match by finding the value that is closest but not more than the search key. It therefore returns Finch here, even though Gary is technically closer to the search key. This is an important feature to take note of, since it could impact your results when using this function!

Use case: Sorted strings in the first column

Now that you’re getting more familiar with the function, let’s try an example data set in which the first column has sorted string values, as shown below. There are two examples each for all the three cases available, but with one distinction of is_sorted parameter. Please observe how the VLOOKUP function is behaving.

You can see that the TRUE or FALSE value for is_sorted parameter doesn’t really affect the behaviour when it finds the search_key within the first column of the range. But an interesting development happens when it doesn’t find an exact match for the search_key. In the case of FALSE (row # 8), it was looking for an exact match. But in the case of TRUE (row # 9), it is looking for an approximate match, and instead of Greg, it returns the information for Gary. This behaviour may or may not be desirable depending on your situation!

Unsorted strings in the first column

The data for this case is essentially the same as that used above, except there are non-sorted strings in the first column.

Unlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. Because the data is not sorted, the function cannot give an accurate approximate match. When the data is not sorted, that the results are correct only when I used FALSE for is_sorted.

So, what is the bottom line?

Never lie to Google Sheets in the is_sorted parameter — if you do, it will return the favor!

Multiple matches in the first column

There will be times when you may encounter multiple instances of the same value in the first column. In the example below, Barry occurs twice. What happens if you use the VLOOKUP function in such a scenario?

You may have noticed, it picks up the first Barry it encounters in the list. And the second Barry is not considered.

Drawbacks with the VLOOKUP function

There are two problems with this formula:

1. For looking up search_key, it always uses the first column within the input range. So it is not possible with VLOOKUP function to fetch a cell value that is to the left of the lookup column.
2. The function is not dynamic enough, in that, the column index values are not updated if we inserted a column between the input range.

There’s an alternative that solves the above two problems. Please go through the explanation for the INDEX and MATCH function combination.