Leila Gharani

When Google Sheets users are taking their first step into the world of the “Power User,” one of the first new functions they learn is lookup functions. You use a lookup function in a scenario where you have some known piece of information in a table, like a part number or employee number. However, you need to discover an associated piece of information, like part color or employee salary, in a separate table.

For Google Sheets beginners, the process is completely manual:

  1. Use the value in the first table (example: part number).
  2.  Locate the matching value in the second table.
  3. Discover the associated information (example: color).
  4. Return to the first table and type the color on the same row as the value from Step 1.
  5. Repeat this process dozens or thousands of times

As you can guess, doing this once may be an inconvenience, but doing this procedure thousands of times is potentially impossible.

Google Sheets – The Comprehensive Masterclass

Last Updated April 2021

Bestseller
  • 133 lectures
  • All Levels
4.8 (1,006)

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

Explore Course

Google Sheets’ many lookup options

Google Sheets has many functions that can perform lookup operations. The most basic lookup function is aptly named LOOKUP. If your data set is not very large or complex, the LOOKUP function can be a nice addition to your sheet. However, it won’t take long for you to discover problems that LOOKUP will not solve.

VLOOKUP and HLOOKUP are two other lookup operations that will give you more options for coming up with a solution. These functions introduce directionality (the “V” stands for “vertical” and the “H” stands for “horizontal”) and the ability to find data in columns precisely or approximately. These two functions have become the workhorse lookup functions, and you will find them used in millions of spreadsheets.

But even Superman has his kryptonite. VLOOKUP and HLOOKUP have weaknesses that certain users find unacceptable. This leads to the use of INDEX/MATCH. These two functions, when used separately, have relatively useful abilities. When used together, though, they form one of the more powerful methods of performing lookups. But everything comes at a cost.

INDEX/MATCH can be one of the more brain-twisting function(s) to write. Many users simply give up, go back to using other methods, and simply live with the limitations. Because VLOOKUP/HLOOKUP are relatively easy to understand and use, they have become the most used lookup functions in Google Sheets.

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):

Learning VLOOKUP by example

Because VLOOKUP and HLOOKUP are so similar, and most scenarios have vertically-built lists, we will examine how VLOOKUP searches in detail. The logic of HLOOKUP is the same, just rotated 90 degrees clockwise.

Consider the following 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)

  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.

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:

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

=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.

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)

  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.

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.

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.

Key takeaways

As the VLOOKUP function has existed for decades, you will likely encounter it in many existing spreadsheet solutions. It is important to understand how these functions work and how to format your data to use them properly. 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.

Page Last Updated: February 2021

Top courses in Google Sheets

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

Visually Effective Excel Dashboards
Leila Gharani
4.7 (11,364)
Bestseller
Unlock Excel VBA and Excel Macros
Leila Gharani
4.7 (26,737)
Bestseller
Master Excel Functions in Office 365 - Excel Dynamic Arrays
Leila Gharani
4.7 (1,291)
Highest Rated
Excel Essentials for the Real World (Complete Excel Course)
Leila Gharani
4.7 (5,803)
Highest Rated
Google Sheets - The Comprehensive Masterclass
Leila Gharani
4.8 (1,006)
Bestseller

Courses by Leila Gharani