How to Use VLOOKUP in Google Sheets
How to Use VLOOKUP in Google Sheets GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > Google Apps
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.
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. 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). 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)
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. 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.
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.
How to Use VLOOKUP in Google Sheets
Quickly find data in Google Sheets
By Aaron Peters Aaron Peters Writer Villanova University Aaron Peters is a writer with Lifewire who has 20+ years experience troubleshooting and writing about consumer and business technology. His work appears in Linux Journal, MakeUseOf, and others. lifewire's editorial guidelines Updated on December 2, 2020 Tweet Share Email Tweet Share EmailIn This Article
Expand Jump to a Section VLOOKUP Formula Syntax Step-by-Step: VLOOKUP Example Using It in Google Sheets Tips for Using the VLOOKUP Function 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. These instructions apply to Google Sheets on all platforms.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. 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). 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)
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. 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.