How to Use VLOOKUP: Demystifying the Google Sheets Function
Google Sheets is primarily used as a way to record and analyze data. It’s not a database, but it can be used like one.
One of the first steps toward becoming a “power user” is to use lookup functions. Lookup functions serve a lot like “queries” to a database.
Consider: you have known information in a table, such as a part number or an employee number. But you need to discover an associated piece of information, like a part color or employee salary.
Lookup functions help. There are three major lookup functions:
For Google Sheets beginners, the lookup process is a manual one. A beginner would:
- Look up the value in the first table.
- Locate the matching value in the second table.
- Discover the associated information.
- Return to the first table and type the color on the same row.
- Repeat the process dozens or even thousands of times.
While doing this once is just a minor inconvenience, doing this procedure thousands of times is potentially impossible.
Tip: Google Sheets shares a number of functions with Excel. The LOOKUP, VLOOKUP, and HLOOKUP features are all features that are in Microsoft Excel too. If you ever find yourself wondering whether a corresponding feature is in Microsoft Excel, it probably is.
The many lookup options in Google Sheets: LOOKUP, VLOOKUP, and HLOOKUP
Google Sheets has three main functions for lookup operations: LOOKUP, VLOOKUP, and HLOOKUP. It also has some corresponding functions, such as INDEX and MATCH.
The LOOKUP function is the most basic lookup function provided by Google Sheets. If your data set isn’t very large or complex, the LOOKUP function will work. But it won’t take long for you to encounter problems you can’t use LOOKUP for. It’s generally used to look up a single, discrete item of data as long as you know the key. More importantly, it has trouble with matching “rough” approximations (it will find “Red and Black” if you type in “Red%,” but it will not find “Red and Black” if you type in “%Black”), and it can fail silently.
VLOOKUP introduces directionality to your lookup functions. VLOOKUP stands for vertical. This function is one of the most popular lookup functions available; it can find data in columns either precisely or approximately and is, therefore, more versatile than LOOKUP alone.
HLOOKUP operates just like VLOOKUP but horizontally. Depending on how your data is structured, you’re likely to use one more than the other. But it’s important to know that these options are there. They work the same way, but having two functions for vertical and horizontal lookups mean you can find data with better levels of precision.
What do you do when VLOOKUP and HLOOKUP don’t give you the results that you need? Both of these functions have weaknesses; notably, the inability to use regular expressions (select items that are “sort of” right). INDEX and MATCH are used to find data with greater flexibility. But they are much, much more advanced than the other three LOOKUP functions. HLOOKUP and VLOOKUP are both much easier to use and, consequently, have greater levels of utility to the average user.
The syntax of VLOOKUP
The Google Sheets VLOOKUP function has four arguments:
- Lookup Value: The Lookup Value is the cell in the first table that contains the value you want to find in the second table.
- Table Array: The Table Array is the table that contains the lookup value to search and the adjoining columns of related information. VLOOKUP is hard-coded to scan the left-most column of this range for the item to be discovered.
- Column Index Number: The Column Index Number is the column number (counted left to right) that you wish to return related information.
- Range Lookup (optional): Range Lookup allows you to define whether VLOOKUP returns an error if it cannot find the lookup value (enter “FALSE” for Exact Match). It also returns the closest value that is less than the lookup value when the lookup value is not found (enter “TRUE” or omit for an Approximate Match).
There are also a few rules we must abide by with the lookup table (second table):
- The table’s column you are searching must contain unique values (i.e., no duplicates).
- The searched column must be in the left-most position (or the first column).
- The table must be in ascending order by the first column when performing approximate matches (explained later).
Note that VLOOKUP will return “N/A” sometimes if it’s given the wrong parameters. But it may also fail silently by giving you data that is incorrect. You need to double-check to make sure the data is what you expected.
Learning VLOOKUP by example
Here’s the magic of VLOOKUP and HLOOKUP. Because they’re so similar, you can learn one while learning the other. We’re going to be learning VLOOKUP, but you’ll also be learning HLOOKUP too.
We start with VLOOKUP because most people have their data in vertically-built lists; it’s just the most intuitive way to do so. HLOOKUP is the same, but horizontal.
Our first example will be a table of employee names and salaries.
We have two additional tables of Employee Information and Bonus Scheme data.
First VLOOKUP formula
Our first goal is to take a name from the first table (Column A) and find matching data in the Name column (Column G) of the Employee Information table. Once located, we want to populate Column C in the first table with the Department name (Column I) of the user from this table.
The Employee Information table occupies cell G3 through I33. To make the formula easier to understand, we have named the table “EmployeeInfo.”
Second VLOOKUP formula
Our second goal is to take the Yearly Salary (Column B) from the first table and locate the Salary (Column L) in the Bonus Scheme table. If we find a salary that does not match exactly in the Bonus Scheme table, then we want the closest salary without going over. Once found, we will return the Bonus percentage (Column M) to the first table’s Bonus (Column D).
The Bonus Scheme table occupies cell L3 through M7. To make the formula easier to understand, we have named the table “BonusInfo.”
Discovering the department
To discover the departments for the listed employees, we will select cell C2 and construct the following formula:
=VLOOKUP(A2, EmployeeInfo, 3, False)
- Take the Name “Gary Miller.”
- Send the name to the “EmployeeInfo” table and locate it in the first (left-most) column.
- Once discovered, jump to the third column in the table (Department) to select the associated department.
- Return the associated department to the VLOOKUP formula
Using the “FALSE” argument ensures that we locate the name EXACTLY.
When the VLOOKUP formula repeats down the column of the first table, we see the following results.
So, what happened to Kathrin Nehmeh?
As there was no entry for this employee in the EmployeeInfo table, the VLOOKUP formula returned a default response of “#N/A.” This is known as an “expected error.” There hasn’t been an error with the logic of the VLOOKUP. It simply can’t find the value. There are several ways to handle this issue.
One technique is to nest the VLOOKUP function within an IFERROR function. The IFERROR function will trap the default, non-discovery response of VLOOKUP and replace it with a custom response. The syntax for the IFERROR/VLOOKUP combination is:
=IFERROR(VLOOKUP(all the original logic), response)
Here are two variations on the IFERROR strategy:
- Return nothing (two sets of double-quote with nothing between them)
=IFERROR(VLOOKUP(A2, EmployeeInfo, 3, False), “”)
- Return a custom message
=IFERROR(VLOOKUP(A2, EmployeeInfo, 3, False), “Not Found”)
NOTE: The IFERROR function traps ALL error types. If you wish to only trap #N/A errors while allowing other errors to manifest, replace the IFERROR function with the IFNA function.
Tip: When you’re starting to deal with advanced functions, you’ll find errors more frequently. You always want to create an error for a reason (such as “not found”) rather than having an ambiguous issue.
Discovering the bonus
To discover the bonus for the listed employees, we will select cell D2 and construct the following formula:
=VLOOKUP(B2, BonusInfo, 2)
- Take the Yearly Salary “60,270.”
- Send the salary to the “BonusInfo” table and locate it in the first (left-most) column. If an exact match does not exist, locate the largest value that does not exceed “60,270.” Here, that value is “60,000.”
- Once discovered, jump to the second column in the table (Bonus) to select the associated bonus.
- Return the associated bonus to the VLOOKUP formula.
The absence of “False” in the final argument places VLOOKUP in “approximate match” mode.
NOTE: When using the “approximate match” mode, you must sort the lookup table (second table) in ascending order by the lookup column (first column).
When the VLOOKUP formula repeats down the column of the first table, we see the following results.
Testing for additional data
If we were to add names and salaries to the first table, we see we can use the Department and Bonus columns to discover the additional information. (Don’t forget to fill the formulas down to the newly added rows.)
Limitations of VLOOKUP
With all of VLOOKUP’s usefulness, there are a few areas where it falters a bit. We’ve mentioned before that there are some clear limitations.
First, the VLOOKUP function cannot return data that exists to the left of the column you’re searching. This means that you need to reorder the discovery table to place all needed columns to the right of the search column, or you have to employ more robust solutions that do not have such a limitation, like INDEX/MATCH.
Second, the VLOOKUP function cannot return a “largest without going under” type of search.
Third, if the table you’re searching contains duplicate entries in the discovery column (first column), it will select the first-encountered match. You always perform searches with this top-down approach. It is not possible to do a bottom-up search to discover the last encountered entry.
Tip: These reasons are why Google Sheets is really more of a data formatting or data analysis system. If you need to do more detailed operations, what you really need to do is export your Google Sheets as a CSV and import it into a database system such as SQL.
As the VLOOKUP function has existed for decades, you will likely encounter it in many existing Google Sheets solutions. It is important to understand how these functions work and how to format your data to use them properly. As mentioned, HLOOKUP operates nearly the same — and LOOKUP is just a non-directional, simpler utility.
There are more sophisticated options you can add to this function, like INDEX/MATCH for more complex datasets, but you will find that you will only have to use them rarely. To learn more about VLOOKUP, INDEX/MATCH, and other functions, check out my complete Google Sheets course.
Frequently Asked Questions
How do you do VLOOKUP in Google Sheets?
You can perform a VLOOKUP with the following syntax: VLOOKUP(search_key, range, index, [is_sorted]).
How do I do a VLOOKUP between two Google Sheets?
As with referencing between sheets, you will simply need to put the page name and an exclamation point before any cells. If you’re referencing a variable on Sheet_One, you would put “Sheet_One!” before the cell.
Why won’t VLOOKUP work in Google Sheets?
There are a few reasons VLOOKUP might not work. It may not be able to find the data, you may be searching for a partial match, or you may have selected the wrong cells.
Why is my VLOOKUP showing as a formula?
VLOOKUP will show up as a formula rather than a result if you miss the “=” sign before the formula.
Top courses in Google Sheets
Google Sheets students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.