Effectively work in Google Sheets with the VLOOKUP formula

The VLOOKUP formula in Google Sheets makes it easy to find certain information in large tables. VLOOKUP searches a column from top to bottom for the cells and values you’re looking for. Even in huge directories, the vertical lookup function allows you to quickly find relevant information. The following guide explains how to use the formula correctly with helpful examples.

What exactly is VLOOKUP?

Spreadsheet programs offer users many benefits for working fast and effectively with tables. Expert users are familiar with a range of practical formulas that make working with Excel or Google Sheets even more efficient and convenient. VLOOKUP is one such formula – enabling you to find information and values in huge directories and tables in no time at all. But how do you use Google’s VLOOKUP?

Tip

The formula also exists in the well-known spreadsheet program from Microsoft. VLOOKUP in Excel works similarly to the function in Google Sheets.

Let’s assume you have a massive directory with customer names, customer numbers, and addresses. Each customer entry is assigned to a column, and the product category for which the customer orders products most frequently from your online store is entered. Before calling a customer, you want to quickly find out which product category is relevant to them. Using the customer number and VLOOKUP formula in Google Sheets, you can now find the corresponding value for the search term, without having to go through the entire list manually for the information you need.

VLOOKUP syntax in Google Sheets

Like any other formula, you need to know how VLOOKUP is correctly entered. If the formula isn’t correct, Google Sheets will not be able to properly run the automatic search. The VLOOKUP formula looks like this:

=VLOOKUP([search key];[range];[index];[is_sorted])

The “search key” is the value you’re looking for. In “range,” you define the section of the table that should be searched. “Index” refers to the position in the table that contains the return value of your search. The index is expressed as a simple number in the formula. If your search key is located in column C and you’re looking for the corresponding value in column F, the index will need to be 3, since column F is three columns away from column C.

Note

If you use Google Sheets in another language, it’s doesn’t matter if you enter the English term “VLOOKUP” or the foreign-language version: Depending on the language settings in your Google account, the software will automatically change the formula names.

The optional formula suffix “[is_sorted]” defines whether the column to be searched is sorted or not. If the column isn’t sorted, you should enter “FALSE”; otherwise “TRUE” is the right option. If the parameter is left empty, Google Sheets will automatically set it to “TRUE”. Even though the parameter is optional, it still plays a decisive role in the outcome:

  • FALSE: The function searches for an exact match and returns the first result found.
  • TRUE: The function doesn’t search for an exact match, but instead returns the value that’s closest to the search key.

Let’s consider the following VLOOKUP example in Google Sheets:

=VLOOKUP(A10;A2:C10;3;FALSE)

This formula instructs Google Sheets to run a vertical search in a column that’s three columns away from column A. The cells between A2 and C10 are searched. The search term is entered in cell A10. Google Sheets then outputs the corresponding value in column C.

VLOOKUP in Google Sheets: step-by-step example

It all becomes clearer with an actual VLOOKUP in Google Docs. We’ll use the following table as our initial dataset:

Google Sheets table
The Google table lists lots of information for individual customers of an online shop.

Step 1: Add your workspace for searching for relevant information anywhere below or next to the table. You can simply enter “Search key” and “Output” as headings if you wish. In the first row of the “Search key” column, enter the cell value that you want to search for relevant results.

Search key defined in Google Sheets
In this example, you wish to find out in which product category the customer with the customer number “121245894” ordered the most products. To do this, enter the customer number as the search key in cell A13.

Step 2: Enter the formula for VLOOKUP in Google Sheets where you want to see your search result. In our example, that’s field B13. Enter the following formula there:

=VLOOKUP(A13;$A$2:$E$10;5;FALSE)
VLOOKUP in Google Sheets
With this formula, you ask to see the value five columns away from the value that corresponds with the search key in column A. Google Sheets then searches vertically from top to bottom in column A for the value “121245894” and returns the corresponding value in column E.

Step 3: You will now see the result “Books” in the column you entered the VLOOKUP formula.

VLOOKUP result in Google Sheets
The result is displayed that corresponds with the relevant search key.

Step 4: You can now enter each customer number from column A as a search key. Adjust the formula to the respective position or click on the results field “Books” and drag the blue square down to the cells below. The formula will then automatically adjust and deliver the results you’re looking for.

VLOOKUP results in Google Sheets
Depending on which search key you enter in the formula and which index you choose, you’ll be shown the corresponding results. For instance, row 15 contains the request for the number of purchased products for customer number “236849356.” In row 16, the ZIP code for the customer name “Lehms” is requested. Here, the table range to be searched had to be defined.
Was this article helpful?
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.