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: 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: 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. 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: 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: 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.
