The Ultimate Guide to VLOOKUP in Google Sheets

If you work with large volumes of interconnected data in spreadsheets, finding and referencing information across multiple tables can be challenging. That’s where VLOOKUP comes in. VLOOKUP is a powerful but often overlooked function in Google Sheets that allows you to search for matching data between two distinct tables.

When used correctly, VLOOKUP can simplify spreadsheet processes and accelerate data analysis. In this guide, we will cover the basics of VLOOKUP, including how to use it in Google Sheets.

What is VLOOKUP in Google Sheets?

VLOOKUP is a search function that finds data in one table and uses that data in another table. The tables can be on the same sheet, a different tab, a separate sheet, or a spreadsheet file. The function searches a specified column vertically for a search key (hence the VLOOKUP), and then returns the value from the same row.

Syntax and Function of VLOOKUP in Google Sheets

Let’s untangle the basics of a VLOOKUP formula to better understand how it works. Here’s an overview of the standard syntax:

VLOOKUP(search_key, range, index, [sorted])

• index is the column containing the value you’re looking for. For example, if you want to find the price of a product in a table, it would be the price column. The index value must be between 1 (first column) and the total number of columns.

• search_key is the item or value you want to find. For example, if you’re searching for a product in your stock table, the search_key could be the item number or the product name in the same row.

• range refers to the range you want to include in the VLOOKUP function or the data columns.

• sorted refers to the final value and is either true or false (true by default). It is used to indicate whether the index column has been sorted.

Key Points of VLOOKUP in Google Sheets

While VLOOKUP is a powerful function, it is not without its limitations. Consider these factors before using VLOOKUP to avoid potential errors.

• VLOOKUP doesn’t differentiate between uppercase and lowercase letters. The function doesn’t distinguish between uppercase and lowercase letters.

• VLOOKUP doesn’t search columns to the left of the specified range. To search for left columns, you need to use an Index Match formula.

• When the sorted parameter is set to TRUE, you need to sort the first column of the range in ascending order.

• VLOOKUP can perform searches using partial matches based on wildcard characters: asterisk (*) and question mark (?).

• Set sorted to FALSE to get exact matches. For example, if your VLOOKUP from another sheet is returning incorrect results.

How to Use VLOOKUP in Google Sheets

Video tutorial: How to Use VLOOKUP in Google Sheets

Now that you have a better understanding of the function, let’s get down to business and see how to use VLOOKUP in Google Sheets. Here are several ways to use VLOOKUP in Google Sheets. You can also check out our video tutorial above for a complete demonstration on using VLOOKUP.

1. VLOOKUP in the Same Google Spreadsheet

Suppose you have a spreadsheet containing your product IDs, a pricing scheme, prices, and subscriptions for your SaaS products.

You want to create another table in the same spreadsheet that only contains the product ID and price. To do this, you can use VLOOKUP to extract the price from the spreadsheet using only the product ID number. Here’s how:

1. Create another column for the product ID and price.

2. Enter the product ID number you want to find, then in the field right next to it, under the Price column, type =VLOOKUP and enter the formula parameters.

3. Enter the value of the product ID in the formula or click on the cell (F2) to add it automatically. Then add a comma.

4. Add the range (second parameter) and specify the column range you want to include in the search. Click on the header of the first column and drag your mouse to the last column of the table, or type A:D, then add a comma.

5. Enter the column number containing the value you’re looking for. The price is the third column in the sequence, so type ‘3’. Then add a comma.

6. Enter “False” for sorted.

7. Your final formula should look like this: =VLOOKUP(F2,A:D,3,FALSE)

8. Press Enter and you should see the result appear in the cell with the VLOOKUP formula.

9. Drag the cell down to copy the formula to the rest of the cells in the column.

You will quickly get the data you need without manually searching for each corresponding value and entering them into the new table.

AI + Google Sheets: Use the Formula Builder to Automatically Generate VLOOKUP Formulas

You can also use the Formula Builder from Coefficient to automatically create the formulas for this example. To use Formula Builder, you need to install Coefficient. Installation takes less than a minute.

Here’s how to install Coefficient from the Google Workspace Marketplace. Or you can bypass the Marketplace and start for free from our website.

1. Click on “Extensions” in the Google Sheets menu. Choose “Add-ons” -> “Get add-ons”. This will display the Google Workspace Marketplace. Here is a direct link to Coefficient’s listing on the Google Workspace Marketplace.

2. Search for “Coefficient”. Click on the Coefficient app in the search results.

3. Accept the prompts for installation. Once the installation is complete, go back to “Extensions” in the Google Sheets menu. Coefficient will be available as an add-on.

4. Launch the app now. Coefficient will run in the sidebar of your Google Sheets. Select GPT Copilot in the Coefficient sidebar.

5. Next, click on “Formula Builder”.

6. Enter a description of a formula in the text box. For example, type: Find F2 in range A:D and return the value from column 3.

7. Then press “Build”. Formula Builder will automatically generate the formula based on the given example.

2. VLOOKUP with Wildcard Characters

If you only know a part of the search key, you can perform a VLOOKUP search for partial matches using the following wildcard characters:

• The question mark (?) to match a single character
• The asterisk (*) to match sequences of characters

Suppose you want to retrieve information about a specific product in the same table.

If you don’t know the complete product ID but remember the first letter “A”, you can use an asterisk to fill in the missing part in your formula:

=VLOOKUP("A*",A:D,1,FALSE)

Press Enter and see the result:

You can create a more flexible VLOOKUP formula by entering the known part of the search key in one cell and adding the asterisk (*) to it. Then set the sorted parameter to TRUE.

For example, you can extract the corresponding pricing plan using this formula:

=VLOOKUP($F$1&"*",A:D,2,TRUE)

To get the actual price, use this formula:

=VLOOKUP($F$1&"*",A:D,3,TRUE)

3. Case-Sensitive VLOOKUP

As mentioned earlier, VLOOKUP doesn’t differentiate between uppercase and lowercase letters. If you need a case-sensitive search, use INDEX MATCH with the EXACT and TRUE functions in your Google Sheets VLOOKUP array formula:

=ArrayFormula(INDEX(return_range, MATCH(TRUE,EXACT(search_range, search_key),0)))

Using the example table below, let’s assume the search key is in cell A3, the search range is G3:G8, and the return range is F3:F8.

The formula should look like this:

=ArrayFormula(INDEX($F$3:$F$8, MATCH (TRUE,EXACT($G$3:$G$8, A3),0)))

The formula can differentiate between uppercase and lowercase characters (a-1001 and A-1001).

4. VLOOKUP on Another Sheet in the Same Spreadsheet File

Suppose you have an “Employees” table on one sheet and a “Sales” table on another sheet.

Here’s how to retrieve the hourly rates corresponding to employee IDs E010 and E040 from the “Employees” sheet and display them in cells B3 and B4 of your “Sales” sheet.

1. Select the target cell.

2. Enter =VLOOKUP(A3,.

3. A3 is the cell that contains the value you want to search.

4. Set the search range to the specific columns of the “Employees” sheet.

5. Lock the cell range by pressing F4 (or Ctrl+F4) on your keyboard. This ensures that these references won’t change when the formula is copied to other cells in the column.

6. The VLOOKUP formula should now look like this: =VLOOKUP(A3,Employees!$A$3:$C$8.

7. Add a comma after the last quotation mark followed by the index of the column containing the values you want to retrieve.

8. Enter the number ‘3’ and add a closing parenthesis.

9. Press Enter and you should see the hourly rate corresponding to employee ID E010 in cell B3 of your “Sales” table.

10. Drag the formula down to fill the rest of the cells in the column.

5. VLOOKUP on Another Sheet in a Separate Spreadsheet File

Performing a VLOOKUP search on another sheet in a separate spreadsheet file uses the same function as the previous example.

However, the second parameter is slightly different—it includes the IMPORTRANGE function.

The IMPORTRANGE function is typically used to import cell values from a separate spreadsheet file into your current spreadsheet.

It has two parameters:

• spreadsheet_key is the URL of the spreadsheet you want to import and must be specified within quotes (” “).

• range_string is a reference to the cell ranges you want to import. It should include the sheet name along with the cells you want to import. For example, if you’re importing cells A3:C8 from the “Employees” sheet in the previous example, your range_string should be “Employees!A3:C8”.

Suppose you have:

• An “Employees” table in a sheet named “Employees” in a spreadsheet file called “Ss1”.

• A “Sales” table in a sheet named “Sales” in another spreadsheet file called “Ss2”.

In this example, we will retrieve the hourly rates corresponding to employee IDs E010 and E014 from the “Employees” sheet in “Ss1” and display them in cells B3 and B4 of the “Sales” sheet in “Ss2”.

Here’s how:

1. Click on B3 in the “Sales” table in “Ss2”.

2. Enter =VLOOKUP(, then select cell A3, followed by a comma.

3. Insert the IMPORTRANGE function as the second parameter, then an opening parenthesis.

4. Open “Ss1” and select the “Employees” sheet.

5. Copy the URL of the Google spreadsheet from the browser’s address bar.

6. Go back to the “Ss2” spreadsheet and paste the URL in the IMPORTRANGE function within quotes.

7. Add a comma after the last quotation mark, followed by the source sheet name – “Employees” in this case.

8. Add an exclamation mark (!) and enter the cell range ($A$3:$C$8) you want to search in the source sheet.

9. Wrap the complete parameter (source sheet name, exclamation mark, and cell range) in quotes, followed by a comma and a closing parenthesis (to close the IMPORTRANGE function).

10. The formula is now: =VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Od_qTN0ze4nvh4cfoDjYCHxPJmdjAqTEABrvLom0KM4/edit#gid=870777933","Employees!$A$3:$C$8"),3).

11. Add a comma after the last quotation mark, followed by the index of the column containing the values you want to retrieve – the hourly rates in the third column of the range A3:C8.

12. Press Enter. You should now see the hourly rate corresponding to employee ID E010 in cell B3 of your “Sales” table.

13. Drag the formula down to fill the rest of the cells in the column.

VLOOKUP can be a powerful function once you understand how to use it to reference information across multiple Google Sheets. Data changes in the source sheet are automatically updated in the connected cells, making it easy to reference data across multiple Google Sheets quickly.

Conclusion

VLOOKUP eliminates the hassle of manually searching and referencing cells in Google Sheets. That’s why Google Sheets users, whether they are scientists or payroll administrators, consider VLOOKUP indispensable. While VLOOKUP comes in many forms, the result is always the same: you save time and energy by avoiding tedious tasks. Now you can focus on the tasks that truly matter.

Start using VLOOKUP in Google Sheets today to simplify your spreadsheet processes and accelerate your data analysis. For more information on Google Sheets tools and tips, visit Crawlan.com.