VLOOKUP Function in Google Sheets

The VLOOKUP is one of those well-known functions everyone’s heard of, yet it’s still somewhat of an enigma. The wonderful thing is that this function is not that difficult to understand; like most things, it is always best explored with an example.

We’ll look at a typical use case in this tutorial to demonstrate its ability, but first, we need to break down the syntax that makes up this function.

Syntax

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

The VLOOKUP performs a vertical search within a table's first column of a range of data. The lookup searches for a search_key in the first column within a range of a table specified by a column index.

In other words, we tell the function to find the result in a particular column where the first column is index number 1. This will make more sense in a moment.

The function has one final argument: to define whether we require an exact match or the nearest match. Generally speaking, it is recommended to use an exact match known as the is_sorted argument, but we’ll cover this later.

We’ll start with these two tables; the main table on the left has four columns and 50 rows of data, and the summary table on the right is where we’ll perform the VLOOKUP function. We will search for the company name and return the industry it represents.

To begin, we need to specify our search_key from the summary table. In this situation, we’re asking to search for the company ‘Walmart’.

=VLOOKUP(F2

Next up, we need to define where to find this search_key. The vital point is that the first column in the range must contain the search_key to return a result.

The range we require, therefore, must begin in column B, not column A, since the company name will not be found in column A as this contains numbers, not company names.

You’ll notice the search_key and range are separated by a comma. This is because these commas are required between each argument in the function.

=VLOOKUP(F2,B1:D51

Okay, so at this point, we’ve specified the search_key from the summary table, and we’re telling the function to search in the main table range, B1:D51.

Next, we need to specify the column index from the main table range we want to return. Since this range is B1 to D51, it contains three columns, the first column in this range has an index of 1; therefore, column C = 2 and column D = 3.

We’re looking to find the industry associated with the company, which would be column C, that is index 2.

=VLOOKUP(F2,B1:D51,2

Now that I’ve specified an index of 2, you can see Google is suggesting that the result will be ‘Healthcare’, but we can see that Walmart is listed as ‘Retail’ in the main table. The reason for this is we need to complete the function by defining the last argument of is_sorted.

As alluded to above, setting this argument to an exact match entered as FALSE in the function is recommended.

Essentially, we’re telling the function that the table is not sorted, so it should not return the first match; it needs to return the exact match. The result could be anywhere in the column, and setting is_sorted to FALSE will ensure that the result will be an exact match.

=VLOOKUP(F2,B1:D51,2,FALSE

You can see now that FALSE has been entered; Google is suggesting that the result will now be ‘Retail’. Finally, we just need to close the parentheses to complete the formula, but it’s good practice to lock the range before doing so.

Locking the range will ensure we can copy the formula down to the other rows in the summary table without changing the range. I’m referring to absolute and relative ranges when locking the range. We need this range to be an absolute range, not a relative range.

To do this, just put your cursor somewhere in the range text B1:D51 and click F4 on your keyboard to add the dollar symbols before the column and row numbers $B$1:$D$51. Of course, if you prefer, you can add the dollar symbols manually, but the F4 is a handy shortcut key.

Now, just hit enter, and you’re done!

The final formula is:

=VLOOKUP(F2,$B$1:$D$51,2,FALSE)

Since we locked the range, Google is suggesting to auto-fill the remaining rows in the summary table, which is perfect; this will save time, so go ahead and click the green tick to accept it.

If you don’t see this for whatever reason, you can just click and drag the little blue square to drag this formula down to the remaining rows.

Our final summary table looks like this.

Have a go yourself with the Google Sheets VLOOKUP template file used in this tutorial. If you like this, you should also add the XLOOKUP to your repertoire.

There are just a few things to watch out for regarding the is_sorted argument.

1. If you do not add a TRUE or FALSE argument, then by default, TRUE will be used, which will return the nearest match, (less than or equal to the search key). In this example, the value is a word; therefore, you would see #N/A returned.

2. Suppose the is_sorted argument is set to TRUE or omitted, and your search column is not sorted. In that case, you’ll likely get an incorrect result returned, so it’s best to always stick to the FALSE argument unless you know you require a TRUE argument.

We’ll explore a VLOOKUP using the TRUE argument in another tutorial as it’s worth knowing how both operate respectively; however, you’re much more likely to use the FALSE argument for most VLOOKUP functions, so it’s best to get this one figured out first.

Check out this post if you want to return multiple columns with the VLOOKUP function. It’s a really useful trick that not many people know about!