Google sheets’ VLOOKUP function performs a Vertical Lookup, identifying a particular value in a column and returning a value from the proper row in a corresponding column.
Imagine you’re looking at the nutrition facts box on the package of a food item, one column lists the categories in question—calories, fat, cholesterol, and so on—with corresponding columns identifying the total amount of each category and its percent of daily value. If you want to know what percentage of daily fat intake the food provides, you simply move your eyes down to the Fat category and scan to the right to find the daily value.
The VLOOKUP function automates that process, allowing Google Sheets to quickly perform this vertical lookup as the basis for more complicated operations. We’ll show you how to use the VLOOKUP function.
=VLOOKUP(search_key, range, index, [is_sorted])
Where:
search_key=
The value in the table you want to identify with the function. In our example, this would be “Fat.”
range=
The full range of cells the function will be searching, with the left-most one containing the search key and the rightmost one containing the vertical list with the result you’re looking for. In our example, this would be the whole nutrition facts table.
index=
The column that contains the result you’d like returned by the function. When you add the range to the previous argument, it creates a numeric sequence, with the leftmost column being 1 and each column to the right taking an additional number. If our range is a nutritional facts table noting calories, fat, and percent of daily value, for the function the category would be 1, the calories 2, fat 3, and percent daily value 4.
[is_sorted]=
The final, optional argument of the function indicates whether or not the column being searched is sorted (true) or not (false). If you omit the argument, it defaults to true. In most cases, you’ll want to select false. Only select true if the column is sorted in ascending order and if you want the function to return a partial match for the search key if no exact match is found.
There are a variety of different ways to use the VLOOKUP formula in Google Sheets. We’ll walk you through the VLOOKUP function’s essential uses.
The VLOOKUP function is often used, like many other functions, to more efficiently perform complicated or tedious tasks, but its most straightforward use case is a simple vertical lookup. Let’s say you want to pull the total sales numbers for a particular author from a publisher’s spreadsheet identifying sales across a whole season. The VLOOKUP function can do it for you.
To pull the sales totals for titles from the author Jamie T. Abernathy, we select A6, the reference cell containing his name.
In this case, we select A2 through D10 to pull from the whole range of data on the sales spreadsheet.
The sales total is contained in column D, the fourth column included in our range, so we enter the number 4.
Since we’re dealing with unsorted data, we must enter “false” for the is_sorted argument, as in most cases with VLOOKUP.
Completing the function reveals the sales total we were looking to identify.
VLOOKUP can also be used to automate a comparison between tables involving some of the same data. Continuing with our publisher’s spreadsheet, let’s say you wanted the first sheet to also indicate the sales target for each type of book format.
For simplicity’s sake, we’ll move all the data to a single spreadsheet, creating a lookup table.
Our search key will be drawn from data in the first table—in this case, the format of each book, or cell F3 for the first item in the list.
The lookup table will provide our range. Since we’re using a reference table, we want to use absolute cell references. This is accomplished by adding $ signs around the cell references in question—or most simply, by hitting the F4 key with the range cells selected.
With our range spanning two columns and the second one containing the sales target numbers we want included on the first spreadsheet, our index is “2.” Since the data is not sorted, we once again add “false” for the final argument.
Completing the function will return your result, and a suggested autofill to complete the rest of the column. Type Ctrl plus enter to fill the rest and save yourself some tedious effort!
Our examples so far have involved finding an exact match, with the final is_sorted argument labeled “false.” VLOOKUP is also a powerful tool when searching for an approximate match with a “true” is_sorted argument.
Let’s say the publishing company offers authors a royalty escalator, with rates increasing alongside sales. We want the spreadsheet to indicate the appropriate royalty rate for each title without having to go back and forth between spreadsheets. When searching for a partial match, VLOOKUP can compare the sales floor against the sales data for each book title and populate the cell with the appropriate rate.
To use VLOOKUP with a partial match, we must begin with a table sorted in ascending order. Here our sales targets for royalties are indicated from lowest to highest.
As when joining data from different tables with VLOOKUP, our search term will be drawn from the Copies Sold column.
Our range here comprises our sorted lookup table—with the cell references locked by the $ sign.
The royalty rate, the data with which the function will populate the new cell, is drawn from the second column of our range, indicated by the number 2.
When searching for a partial match, the is_sorted argument must be marked as “True” (or left blank, thus defaulting to “True”). Now the function will take the sales data and search it against the range until it reaches a number larger than the value, stopping there to populate the new cell with the corresponding royalty rate.
As with many other Google Sheets functions, you can use wildcard operators to search for partial matches when using VLOOKUP. For this function, Google Sheets supports the use of two wildcard operators:
We can return to our initial spreadsheet for an example. Let’s say you’re trying to identify the title of a recent publication, but you can only remember a portion of the author’s name. Google Sheets can come to the rescue with the use of the VLOOKUP function and wildcard operators.
Here, the wildcard will come into play for our search key in the formula. Say you remember the author’s name starts with the letters “Jam.” Encase those letters in quotation marks and conclude with the asterisk wildcard.
To return the title associated with this partial name, the range for our formula will cover columns A–E.
Column B, the second in our range, contains the title we’re looking for, making our index 2.
With our final formula reading =VLOOKUP("Jam*",A3:E11,2,false, we simply close the parenthesis and hit enter to reveal the result: Guilty: A Ruth Willard Mystery.
In Google Sheets, the VLOOKUP formula will only pull data from columns to the right of your search term. If you want to draw data from the left, you’ll have to use the MATCH function.
There are a few common mistakes that prevent the VLOOKUP function from successfully completing.
The VLOOKUP function will only perform a vertical lookup—searching up and down columns for a value. If you want to perform a horizontal lookup—searching across rows within a range for a value—you’ll need to use the Google Sheets HLOOKUP function.
Click here to access our sample sheet so that you can see our examples and practice.
We hope this article has helped you and given you a better understanding of the VLOOKUP Google Sheets formula. You might also like our articles on how to use Google Finance in Sheets and how to use HLOOKUP in Google Sheets.
To optimize your workflow, we recommend reading our guide on how to make a hyperlink to email someone in Google Sheets.
