How to use the VLOOKUP Google Sheets formula

Written by Valentine Schelstraete

Mar 29, 2017

Given its utility, VLOOKUP Google Sheets formula is perhaps one of the most widely used formulas 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. We can expect an error if the key doesn’t exist.

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

  • search_key – is the value that the VLOOKUP formula 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 formula 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 formula 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.

Usage: VLOOKUP Google Sheets Formula

Use case: Numbers in the first column

Let us try out on a few examples. Here is a test data with sales figures generated by a set of salespersons. We tried to answer various business case scenarios (column D), using this formula. You’ll observe how the index parameter value affects the outcome.

Here’s an interesting thing to note in the last case (row # 7 above). The formula 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 column. Therefore, it cannot return an appropriate value.

The curious case of [is_sorted]

In the examples, we have chosen FALSE for the last parameter if the first column within the input range is not sorted in ascending order. We chose TRUE otherwise. Now, what if we tried to do the opposite?

The second case worked out well. But the first one, not as much! Why is that? Here is a note from the official documentation of VLOOKUP formula:

If the is_sorted argument is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned.

So, there, they said it themselves! It will not give us expected results when we go with the TRUE option when the first column is not sorted. However, if we have sorted values in the first column, using TRUE for the last parameter will result in a much better performance.

Use case: Sorted strings in the first column

Now that we got our hands greased with numbers, let us try with an example dataset, 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 formula is behaving.

Apparently, the TRUE or FALSE value for is_sorted parameter doesn’t really affect the behavior when it finds the search_key within the first column of the range. But an interesting development happens when it doesn’t find a 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 apparently is looking for a near match. This, obviously, may not be desirable in many cases.

Use case: Unsorted strings in the first column

The data we are considering this use case is essentially the same as that used above. Except, we have non-sorted strings in the first row.

Unlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. And we can deduce that the results are correct only when we used FALSE for is_sorted.

So, what is the bottom line?

Never lie to Google Sheets in the is_sorted parameter! Seemingly, it returns the favor!

Use case: Multiple matches in the first column

There will be instances where we may encounter multiple instances of the same value in the first column. In the example below, Barry occurred twice. What happens if we use VLOOKUP formula in such scenario? Let’s find out.

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

Use case: Drawbacks with the VLOOKUP formula

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 formula to fetch a cell value that is to the left of the lookup column.
  2. The formula 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 formula combination here.

You may also like…

Share This