Leila Gharani

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. 

Laptop, spreadsheet, hands, typing

Lookup functions help. There are three major lookup functions:

For Google Sheets beginners, the lookup process is a manual one. A beginner would:

  1. Look up the value in the first table.
  2. Locate the matching value in the second table.
  3. Discover the associated information.
  4. Return to the first table and type the color on the same row.
  5. 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.

Google Sheets – The Comprehensive Masterclass

Last Updated October 2021

Bestseller
  • 133 lectures
  • All Levels
4.8 (2,179)

Learn Google Sheets in 2021! Solve Complex Data Analysis Problems. Become an Expert: Learn Pivot Tables, QUERY & more | By Leila Gharani

Explore Course

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.

LOOKUP

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

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

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.

INDEX/MATCH

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:

There are also a few rules we must abide by with the lookup table (second table):

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.

example, google sheets, table, employee name, salary

We have two additional tables of Employee Information and Bonus Scheme data.

Google sheets example, employee information, 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)

  1. Take the Name “Gary Miller.”
  2. Send the name to the “EmployeeInfo” table and locate it in the first (left-most) column.
  3. Once discovered, jump to the third column in the table (Department) to select the associated department.
  4. Return the associated department to the VLOOKUP formula

Using the “FALSE” argument ensures that we locate the name EXACTLY.

Google sheets, example, false argument

When the VLOOKUP formula repeats down the column of the first table, we see the following results.

Google sheets, example, 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:

=IFERROR(VLOOKUP(A2, EmployeeInfo, 3, False), “”)

Google sheets, Vlookup example, iferror

=IFERROR(VLOOKUP(A2, EmployeeInfo, 3, False), “Not Found”)

Google sheets, vlookup example, 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)

Google sheet, vlookup example, bonus info
  1. Take the Yearly Salary “60,270.”
  2. 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.”
  3. Once discovered, jump to the second column in the table (Bonus) to select the associated bonus.
  4. 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.

google sheets, Vlookup example, bonus 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.)

google sheets, example, testing for additional data

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.

Key takeaways

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.

Page Last Updated: September 2021

Top courses in Google Sheets

Google Sheets - The Comprehensive Masterclass
Leila Gharani
4.8 (2,179)
Bestseller
The Complete Google Sheets Course: Beginner to Advanced!
Evan Ramsey, Sansone Partners
4.6 (5,030)
Bestseller
Google Sheets Fundamentals
Framework Tech Media, Mark Lassoff
4.8 (63)

More Google Sheets Courses

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.

Request a demo

Courses by Leila Gharani