How to Use VLOOKUP in Google Sheets

VLOOKUP, or "Vertical Lookup," is a useful function that goes beyond using your spreadsheets as glorified calculators or to-do lists, and do some real data analysis. Specifically, VLOOKUP searches a selection of cells by column for a value, then returns you a corresponding value from the same row. Knowing what "corresponding" means in this context is the key to understanding VLOOKUP, so let's dive in and take a look at using VLOOKUP in Google Sheets.

Using the VLOOKUP Formula Syntax

VLOOKUP is a function you use in a formula, although the simplest formula is to just use it on its own. You need to supply a couple of pieces of information to the function, separated by commas, as follows:

VLOOKUP(YOUR SEARCH TERM, CELL RANGE, RETURN VALUE, SORTED STATE)

Let's look at each one of these in turn.

• YOUR SEARCH TERM: This is referred to as the search_key in the documentation, but it's the term you want to find. It can be a number or a bit of text (i.e. a string). Just make sure if it is text that you enclose it in quotes.
• CELL RANGE: Referred to as simply the range, you use this to select which cells in your spreadsheet you'll search through. Presumably this will be a rectangular region with more than a large number of columns and rows, although the formula will work with as little as one row and two columns.
• RETURN VALUE: The value you want to return, also called the index, is the most important part of the function, and the trickiest to understand. This is the number of the column with the value you want to return relative to the first column. Stated another way, if the first (searched) column is column 1, this is the number of the column for which you want to return the value from the same row.
• SORTED STATE: This is designated as is_sorted in other sources, and it's a true/false value on whether the searched column (again, column 1) is sorted. This is important when searching for numeric values. If this value is set to FALSE, then the result will be for the first perfectly matching row. If there are no values in column 1 that match the search term, you'll get an error. However, if this is set to TRUE, then the result will be the first value less than or equal to the search term. If there are none that match, you'll again get an error.

The VLOOKUP Function in Practice

Suppose you have a short list of products, each of which has an associated price. Then, if you want to fill a cell with the price of a laptop, you'd use the following formula:

=VLOOKUP("Laptop",A3:B9,3,false)

This returns the price as stored in column 3 in this example, which is the column two over to the right from the one with the search targets.

Let's take a look at this step by step to explain the process in detail.

1. Place the cursor in the cell where you want the result to appear. In this example, it's B11 (the label for this is in A11, "Laptop Price," although this doesn't feature into the formula).

2. Start the formula with the equal sign (=), then enter the function. As mentioned, this will be a simple formula that consists only of this function. In this case, we're using the formula:

   =VLOOKUP("Laptop",A3:C9,3,false)
   
   

3. Press Enter. The formula itself will disappear in the spreadsheet (although it will still appear in the Formula Bar above), and the result will show instead.

4. In the example, the formula looks at the range A3 to C9. Then it looks for the row containing "Laptop." It then looks for the third column in the range (again, this includes the first column), and returns the result, which is $1,199. This should be the result you want, but if it looks strange double-check the parameters you entered to make sure they're correct (especially if you copied-and-pasted the formula from another cell, because the cell range may change as a result).

Once you get the hang of how to select the range and its relative return value, you can see how this is a handy function to find values even in very large data sets.

Using VLOOKUP Across Different Google Sheets

With regard to the CELL RANGE parameter, you can perform your VLOOKUP not only on cells within the current sheet, but within other sheets in the workbook as well. Use the following notation to specify a cell range in a different sheet in your current workbook:

=VLOOKUP("Laptop",'Sheet name in single quotes if more than one word'!A1:B9,3,false)

You can even reach into cells in an entirely different Sheets workbook, but you need to use the IMPORTRANGE function. This takes two parameters: the URL of the Sheets workbook you want to use, and a range of cells including the Sheet name as shown above. A function containing all these items might look like this:

=VLOOKUP("Laptop",IMPORTRANGE("https://docs.google.com/spreadsheets/d/aLlThEnUmBeRsAnDlEtTeRs/","Sheet1!B7:D42"),3,false)

In this example, the nested function (i.e., the result of the IMPORTRANGE function) becomes one of the parameters of the VLOOKUP function.

Tips for Using the VLOOKUP Function

To make sure you get the right results from your formula, keep the following points in mind.

• First, enclose text-based search terms in quotes. Otherwise Google Sheets will thing it's a Named Range, and give you an error if it can't find it.
• If you're coping and pasting one of these formulas, the normal rules on updating the value of the cell range still apply. In other words, if you have a fixed list of data, make sure you anchor the cell range with the dollar sign (i.e. "$A$2:$B$8" instead of "A2:B8"). Otherwise the formula will be offset depending on where you paste them (note the screenshot at the beginning of the section, where row numbers are off by one).
• If you sort your list, remember to revisit your lookups in the event you sort it again. The shuffling of rows may give you unexpected results if you set the sorted state of the formula to TRUE.