Learn How and When to Use the VLOOKUP Function in Google Sheets

Are you tired of manually searching and updating data in your Google Sheets? Well, fret no more! In this article, we will explore the power of the VLOOKUP function in Google Sheets and learn how and when to use it. So, grab a cup of coffee and get ready to revolutionize your data management skills!

Syntax of the VLOOKUP function in Google Sheets

Let’s start with the syntax of the VLOOKUP function in Google Sheets. The general format is as follows:

VLOOKUP(lookup_value, range, index, [is_sorted])

Where:

• lookup_value – The value you want to search for in the data range. It is also known as the search value or unique identifier.
• range – The table of data containing two or more columns, where the data is arranged in vertical rows, and the lookup values are present in the first column of the data table.
• index – The column number within the range from which the data is extracted and is associated with the corresponding lookup value in the same row.
• is_sorted – This determines the type of match you want to return for the lookup value; either an exact match (false) or an approximate match (true). This is an optional input.
  • false – It searches for an exact match.
  • true – It searches for an approximate match, or the closest value or the previous smallest value to the lookup value if no exact match is found. This is the default match type if you don’t mention it in the function.

Some Key Points to Remember

Before we dive into the examples, here are some basic points to remember about the VLOOKUP function in Google Sheets:

• The VLOOKUP function in Google Sheets is case-insensitive. It does not differentiate between uppercase and lowercase values.
• The VLOOKUP function in Google Sheets searches the lookup_value in the first column of the range, which is the leftmost column, and retrieves information from the right columns of the range.
• When is_sorted is true (approximate match), the data range must be sorted in ascending order (from lowest to highest) based on the first column of the range, otherwise, the VLOOKUP function may return incorrect results.
• When is_sorted is false (exact match), it is not necessary to sort the data range in any particular order.
• The VLOOKUP function in Google Sheets can search for a partial match by using wildcard characters such as asterisk (*) and question mark (?). The question mark (?) is used to match any single character, whereas the asterisk (*) is used to match any sequence of characters.

Examples of the VLOOKUP Function in Google Sheets

Now, let’s discuss some examples of the VLOOKUP function in Google Sheets to have a better understanding of its usage. Suppose we have a dataset of cigarette sales for different regions, and we want to update the price, sales amount, and commission percentage based on the sales amount ranges.

VLOOKUP Function in Google Sheets for an Exact Match

Firstly, we need to update the price of each brand in the main data sheet from the price list of each brand based on an exact match. According to the above example, we have a unique price list for each brand in the data range H1:I10.

When the price of each brand in each region is updated, the sales amount is also updated as the sales amount is the product of quantity and price per carton. The Google Sheets VLOOKUP function for an exact match of the unique brands is as follows:

=VLOOKUP(B2,$H$1:$I$10,2,FALSE)

We need to make the range absolute by inserting the $ sign with the column and row references so that the range reference does not change when we copy the formula to other rows.

When we copy the above Google Sheets VLOOKUP formula to other rows in the price column, it retrieves the price of each unique brand from the price list range. As a result, when the price of each brand in each region is updated, the sales amount is also updated as it is the product of quantity and price, as shown below:

VLOOKUP Function in Google Sheets for an Approximate or Closest Match

The VLOOKUP function in Google Sheets searches for the lookup_value based on an approximate or closest match. In this method, the Google Sheets VLOOKUP formula first searches for an exact match of the lookup_value, and if no exact match is found, the formula searches for a value that is the closest match or the previous smallest value to the lookup_value.

In this example, we need to update the commission percentage (%) based on the sales amount, which we have already updated above. We have a data range of the commission percentage (%) with a breakdown of the sales amount. As we are going to search for the value based on an approximate or closest match, we need to sort the range (H12:I19) in ascending order based on the sales amounts.

Now, in column F, we need to update the commission percentage based on the sales amount as the lookup value in column E. We need to use the following VLOOKUP formula in the active cell F2 and then copy it to other rows:

=VLOOKUP(E2,$H$12:$I$19,2,TRUE)

As you can see, we have set the commission percentage based on different sales amount ranges. Thus, the Google Sheets VLOOKUP function has retrieved the commission percentage from the second column for the sales amount (lookup_value) in the first column of the range based on an approximate or closest match when the sales amount is less than or equal to the lookup_value.

Now that you know how and when to use the VLOOKUP function in Google Sheets, you can leverage it to streamline your data search and update tasks in your spreadsheets. To learn more about advanced features of Google Sheets and other productivity tools, visit Crawlan.com.

Remember, with the VLOOKUP function, data management becomes a breeze! Cheers to effortless spreadsheet wizardry!