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?
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.
Google Workspace with IONOS
Work smarter, together. Configure Gmail for your domain and benefit from the familiar Google tools all in one place.
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:
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.
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:
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:
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.
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:
Step 3: You will now see the result “Books” in the column you entered the VLOOKUP formula.
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.