What is VLOOKUP in Google Sheets?
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 Syntax Google Sheets
=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.
How to use VLOOKUP in Google Sheets
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.
Simple VLOOKUP
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.
1. Type =VLOOKUP( in an empty cell
2. Enter the search key
To pull the sales totals for titles from the author Jamie T. Abernathy, we select A6, the reference cell containing his name.
3. Select the range
In this case, we select A2 through D10 to pull from the whole range of data on the sales spreadsheet.
4. Enter the index
The sales total is contained in column D, the fourth column included in our range, so we enter the number 4.
5. Add “false” to the final argument
Since we’re dealing with unsorted data, we must enter “false” for the is_sorted argument, as in most cases with VLOOKUP.
6. Type ) and hit enter
Completing the function reveals the sales total we were looking to identify.
Using VLOOKUP to join data from different tables
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.
1. Copy the data from one sheet to a lookup table in another
For simplicity’s sake, we’ll move all the data to a single spreadsheet, creating a lookup table.
2. Type =VLOOKUP( in the appropriate empty cell
3. Add the search key
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.
4. Add the range using absolute references
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.
5. Add the index and “false”
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.
6. Hit enter and autofill the rest
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!
Using VLOOKUP With an Approximate Match
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.
1. Add a lookup table sorted by ascending value
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.
2. Type =VLOOKUP( in the appropriate empty cell
3. Add the search key
As when joining data from different tables with VLOOKUP, our search term will be drawn from the Copies Sold column.
4. Add the range using absolute references
Our range here comprises our sorted lookup table—with the cell references locked by the $ sign.
5. Select the index
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.
7. Make “True” your final argument
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.
8. Autofill the rest
Using Wildcards for VLOOKUP on Google Sheets
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:
- Question mark (?), to match a single character
- Asterisk (*), to match a sequence of characters
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.
1. Type =VLOOKUP( in the appropriate empty cell
2. Add the search key using a wildcard
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.
3. Select your range
To return the title associated with this partial name, the range for our formula will cover columns A–E.
4. Select your index
Column B, the second in our range, contains the title we’re looking for, making our index 2.
5. Mark is_sorted as “false”
6. Type ) and hit enter to reveal your result
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.
Frequently Asked Questions
What if I want to look up data in columns to the left rather than the right?
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.
Why am I getting an error?
There are a few common mistakes that prevent the VLOOKUP function from successfully completing.
- Unless you’re working with a range whose first column is sorted in ascending order, make sure to fill in the is_sorted argument as “false,” since the formula will default to “true” if left blank.
- Google Sheets’ VLOOKUP function is not case sensitive, so if your search term relies on case sensitivity you will not get the results you need.
What if I want to perform a horizontal lookup?
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.
Sample Sheet
Click here to access our sample sheet so that you can see our examples and practice.
Recommended articles
More resources
