The classic VLOOKUP function in Google Sheets is arguably the most well known “advanced” spreadsheet formula.
VLOOKUP is part of the serious spreadsheet users lingo.
Knowing it signifies you have considerable spreadsheet chops and should be taken seriously.
It’s not difficult to learn if you begin with the concept of the VLOOKUP before getting bogged down in the weeds of the syntax.
And it’s a hundred percent worth your effort to learn. It’s a useful function that you’ll find yourself using frequently once you’ve mastered it.
What does the VLOOKUP function do?
The VLOOKUP function in Google Sheets is typically used to bring data from one data table and add it into another.
For example, suppose you have a list of customer transactions in one table and a list of customer details in a second table. You might want to add customer data, e.g. location, to the transaction data to do a deeper analysis, e.g. where are my best customers located.
Suppose we run a Saas startup for famous writers, who each pay a monthly fee to subscribe to our service:
In addition, we have a customer table that contains their location details:
We don’t see the full picture if we look at these two tables separately.
If we want to truly understand our sales data, we need to combine these tables so we can see revenue by location.
How would we do this?
We would take each name in turn, which is our Search Term, from the original table. We use the name because it’s in both tables. It’s the common element that lets us link the two tables.
Then we look for that name in the customer table, which is our Lookup Table.
When we find the name we’re looking for, we look across that row to the column containing the information we want to retrieve. In this example we want location, which is column 2. This number is called the Index.
We return the value from column 2 to the relevant row of the original table, which now looks like this:
Congrats, you’ve just done your first VLOOKUP!
And that’s the VLOOKUP function in Google Sheets in a nutshell.
There’s a lot more nuance than that, but in essence this is what it does.
Although it’s not particularly insightful in this simplified example, it’s not hard to imagine how helpful it is when you have thousands, or tens of thousands, of rows of data.
VLOOKUP Function in Google Sheets Syntax
Here is the VLOOKUP syntax:
=VLOOKUP(search_key, range, index, [is_sorted])
The name VLOOKUP stands for Vertical Lookup. It gets this name because it searches for a value up and down a column, i.e. vertically, rather than horizontally. The horizontal lookup, which is used much less frequently, is called the HLOOKUP.
The search_key is the item from the original table that you want to search for in the lookup table.
The second argument, range, is the lookup table, i.e. the table we’re going to search in. VLOOKUP searches down the first column of the this table for the search term.
NOTE: VLOOKUP always searches the first column in the lookup table for the search term.
If and when the search term is found, the value from a specific cell of that row is returned. The index value (the third argument) determines how many columns you go across before returning the value.
For example, index value 1 would just return the search value again (because that’s in the 1st column), index 2 would return whatever is in the adjacent column to the right, index 3 returns the value from the 3rd column of the range, etc.
If the index number is greater than the number of columns in the lookup table (i.e. you’re asking the VLOOKUP formula to return values from column 4 of a lookup table that only has 3 columns), a #REF! error is returned.
The final argument, is_sorted, is a TRUE or FALSE value, which you may see written as 1 (TRUE) or 0 (FALSE) occasionally, that indicates whether the column is sorted or not. If it’s omitted, it defaults to a TRUE value.
It determines whether you want to match the search term exactly or find the nearest match.
If you set the fourth argument to FALSE:
- It does exact matching
- It is not case senstive
- If there are multiple matches, the first match is found and the return content is taken from that row (i.e. if Ben Collins appears twice in the lookup table on row 5 and row 10, then information from row 5 is always returned)
- If no lookup value is found, VLOOKUP returns the #N/A error message
- The FALSE setting is used for 99% of VLOOKUP cases
If you set the fourth argument to TRUE:
- It finds the nearest match that is less than or equal to the search key
- This option is rarely used
Example VLOOKUP Function in Google Sheets
Here’s an example of a VLOOKUP formula:
=VLOOKUP( A4 , $I$3:$J$7 , 2 , false )
The search term is the value in cell A4, “Mark Twain”.
The lookup table is the range $I$3:$J$7.
Notice the $ signs around the range references, which make it an absolute reference and lock the range reference in place. This is often required for VLOOKUPs so that when you drag the formula down your column and apply it to new rows, the lookup table stays locked in place. Use the F4 key to quickly add or remove dollar signs.
The index number is 2, meaning the value from the second column of the lookup table will be returned, i.e. the “Address” column.
The final argument is false, meaning this is an exact match.
VLOOKUP function template
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.
Approximate matching using A VLOOKUP Formula with TRUE argument
This VLOOKUP lesson is from Day 6 of my free Advanced Formulas 30 Day Challenge course.
Suppose you know the interest rates at your bank:
And then you have a bunch of accounts for which you want to calculate the interest:
It would be tedious to manually lookup each account value in the interest table and determine the rate.
What you can do is use the VLOOKUP with the TRUE argument.
In this example, I have an ordered list of values in column B, from lowest to highest, that denote the different brackets for the interest rate bands.
When I lookup a value (e.g. $79,891) the VLOOKUP TRUE will search down column B until it reaches a number larger than the search value, then stop on that row before that larger value. In this example, it will stop at $50,001 because the next value, $100,001, is larger than the $79,891. So it returns an interest rate from the $50,001 row because that represents the $50,001 – $100,000 band that the interest rate is in.
It finds the nearest match that is less than or equal to the search key.
The result of the VLOOKUP is:
Using The VLOOKUP Function in Google Sheets To Compare Data Lists
Another common use case for VLOOKUP is to compare two lists of data, for example names, and determine what the differences are.
You compare lists by looking for names from list 1 in list 2 and then vice versa, looking for names from list 2 in list 1.
The VLOOKUP formula might look like this:
=IFERROR( VLOOKUP( A2 , E:E , 1 , false ) , "Not in List 2" )
A few things to note about this formula:
- The lookup table is a single column E:E
- So the index must be 1
- With index 1, it returns the name if it’s found
- It uses the IFERROR function to display a custom error message when names are not found
Here is a list comparison example in practice (click to enlarge):
Advanced VLOOKUP Techniques
VLOOKUP In Google Sheets Using Wildcards For Partial Matches
How to use wildcard characters “*” to perform partial matches in Google Sheets.
How To VLOOKUP To The Left In Google Sheets?
Regular VLOOKUP formulas will only return values from columns to the right of the lookup column. However, there’s a technique using Array Literals that lets us return values from columns to the left of the lookup column.
Have Vlookup Return Multiple Columns in Google Sheets
Have you ever wanted to return more than a single column with VLOOKUP? Perhaps you want to combine two tables and bring all the data across. This tutorial will show you how to do that without requiring multiple VLOOKUP formulas.
VLOOKUP Multiple Criteria in Google Sheets
This tutorial shows you how perform a VLOOKUP using multiple input criteria. For example, you can combine first name and last name inside the VLOOKUP to search against a full name column.
You can also read about it in the Google documentation.