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 by Google with IONOS
Working 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:
=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.
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:
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:
=VLOOKUP(A13;$A$2:$E$10;5;FALSE)
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.
Related articles
Google Sheets vs. Excel
Microsoft Excel and Google spreadsheet programs differ primarily in how they work. While the more conventional Excel is used locally, Google Sheets is used online. Both have their advantages. In this guide, we will compare Microsoft Excel and Google Sheets and show you what you can expect from these spreadsheet programs.
COUNTIF in Google Sheets: how to use the function in Google’s spreadsheet program
Spreadsheet processing is used to easily calculate numbers and values using the SUM function in Excel. But if you want to count only some of the values in a table, you should use the COUNTIF function. The function counts values across a specified cell range according to one or multiple criteria. In this article, we’ll show you how COUNTIF works in Google Sheets.
Robert KneschkeShutterstock How to create a Gantt chart in Google Sheets
Gantt charts are essential visualisation tools in project management. Where events need to be planned or projects have to be organised, these practical charts are often used to visualise tasks and durations. If you’re working with Google’s Google Workspace, you can easily create Gantt charts in Google Docs and share them with colleagues and clients.
Google Sheets: how to add a drop-down list
Drop-down lists in Google Sheets provide useful formatting options for many users. Adding a drop-down list to a cell allows users to choose between lots of different options, or assign values to certain attributes. This step-by-step guide explains how to create drop-down lists in Google Sheets.
