Don’t forget to share this post

When working with large datasets, the information tends to be spread out among different spreadsheets. In how to do VLOOKUP in Excel with two spreadsheets we explain the many benefits of using the VLOOKUP function to easily transfer information between sheets and workbooks in Excel.

Google Sheets also allows you to use the VLOOKUP function to find data between different sheets or spreadsheet files. However, while this function is useful, there are certain disadvantages to using it between different files, particularly if you use it regularly or need multiple instances. Fortunately, there is an easy-to-use solution that overcomes these limitations: Sheetgo. This article will show you how to use the VLOOKUP function effectively in your Google Sheets to quickly identify and move your data. You will also learn how to combine VLOOKUP with IMPORTRANGE, as well as how to use Sheetgo to avoid the disadvantages of using IMPORTRANGE.

How to use Google Sheets to VLOOKUP data from another sheet?

VLOOKUP formula is an abbreviation for “vertical lookup”. This means that the formula is used to search for a key in the first column of a specified range. It then returns the value of the specified cell with the range’s row.

Just like in Excel, Google Sheets also allows you to VLOOKUP from another spreadsheet within a file, or even another Google Sheet file altogether. First, let’s break down the formula for Google Sheets.

Google Sheets VLOOKUP Syntax

VLOOKUP(search_key, range, index, [is_sorted])
  • search_key: refers to the key or value you perform the search for.
  • range: is the data considered for the search. The first column of the selected range is searched for to find the search_key.
  • index: refers to the first column in the range where the search_key will be searched for. The index refers to the column position - 1, 2, 3, etc.
  • [is_sorted]: is used to indicate whether the first column to be searched is sorted. Since it is set to “true” by default, which returns the closest match, you need to specify “false” in order to search for an exact match.

Let's see how the VLOOKUP formula works in Google Sheets with a practical example.

Let’s say I am responsible for an international internship program. For this, I need to collect, structure, and update data for students, universities, and companies where the internship takes place. I am currently updating the ‘STUDENT INFO’ sheet in my Google Sheets file. Here, I have distributed the student-related data among three different tabs or sheets titled ‘MAIN’, ‘FILE’, and ‘PROGRAM’.

Since I will be using the first tab for most daily tasks, I want to keep the basic student information here; student ID, student’s university, and student’s email address. Let’s see how I can VLOOKUP the email address in Google Sheets from my other ‘FILE’ tab.

  1. 1. Start on the Google Sheet where you wish to add your VLOOKUP results. In this example, I will add my data to the 'STUDENT INFO' file, under a column called 'Email'.
How to use Google Sheets to VLOOKUP data from another sheet Enter formula name
  1. 2. The ‘Student ID’ will be the search_key, so you simply need to click on cell A2 to include it as the first argument of the VLOOKUP formula.
How to use Google Sheets to VLOOKUP data from another sheet Search key
  1. 3. To select your range, head to the tab where your data is located and select a range of cells that include your search_key and target_value. In this example, I need to head to my 'FILE' tab and select a range of cells that includes the ‘Student ID’ and ‘Email’ columns.
How to use Google Sheets to VLOOKUP data from another sheet Range
  1. 4. Type in the index, which refers to the number position of the column where the target value is located. Since we want the ‘Email’, we need to type in ‘5’.
How to use Google Sheets to VLOOKUP data from another sheet Index
  1. 5. As it is case sensitive, we want to VLOOKUP the Google Sheets for an exact match, so we place ‘false’ in the [is_sorted] parameter.
How to use Google Sheets to VLOOKUP data from another sheet Sorted to false
  1. 6. Once all the arguments are included, press Enter and it will automatically return the first value.
How to use Google Sheets to VLOOKUP data from another sheet Press Enter
  1. 7. To apply the VLOOKUP formula in the rest of the cells, select the bottom-right corner of the cell and drag it down to complete.
How to use Google Sheets to VLOOKUP data from another sheet Drag down

Here, we have seen the VLOOKUP in Google Sheets with a single-criteria example. However, Google Sheets allows you to VLOOKUP multiple columns by using multiple criteria. You can retrieve several values distributed in various columns. Its most common use is for first and last names to be joined in one single column.

Another excellent way to save time is to use the VLOOKUP function to transfer data from another spreadsheet in Google Sheets. Let’s explore how to do this method now.

How to VLOOKUP in Excel with Two Spreadsheets
How to VLOOKUP in Excel with Two Spreadsheets?

Sometimes our data may be spread out among different Excel sheets or workbooks. Here's how to do VLOOKUP in Excel with two spreadsheets

READ MORE

How to use VLOOKUP between different Google Sheets files?

Below, you have two different methods for using VLOOKUP between two different Google Sheets files. First, you will learn how to combine VLOOKUP with IMPORTRANGE. However, there are some disadvantages to using this method. The second method uses Sheetgo to avoid the limitations of combining these functions.

How to VLOOKUP between different Google Sheets with IMPORTRANGE

Let’s say that I now want to add data related to the company in the ‘STUDENT INFO’ spreadsheet. However, all the company-related information is on a separate one, entitled ‘INTERNSHIP INFO’. You can easily VLOOKUP data from a different spreadsheet by simply combining it with the IMPORTRANGE formula. We recommend that you read more about this formula in our post on How to use IMPORTRANGE in Google Sheets before getting started with the next steps.

Google Sheets IMPORTRANGE Formula Syntax

IMPORTRANGE(spreadsheet_url, range_string)
  • spreadsheet_url: refers to the URL of the Google Sheet from where data will be imported. It should appear enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.
  • range_string: corresponds to the range of the sheet to be imported and should also be placed within quotation marks followed by an exclamation point. It either be enclosed in quotation marks or appear as a referenced cell.

Let’s see it more clearly in the example.

  1. 1. Go to your original file and start typing the VLOOKUP formula where you want your data to be located. In this example, I will open my ‘STUDENT INFO’ file and type in the formula in the cell under my new ‘Company’ column.
How to use Google Sheets to VLOOKUP data from another workbook Enter VLOOKUP
  1. 2. Now click on the cell that will be your search_key. In this case, it’s the first cell (A2) under the ‘Student ID’ column.
How to use Google Sheets to VLOOKUP data from another workbook Search key
  1. 3. After the comma, include the IMPORTRANGE formula. Google Sheets will prompt the formula as before, so you can directly continue to click on it to include.
How to use Google Sheets to VLOOKUP data from another workbook Enter IMPORTRANGE
  1. 4. Open quotation marks.
How to use Google Sheets to VLOOKUP data from another workbook
  1. 5. Include the spreadsheet_url by copying the link from “...d/” until “/edit…”.
Alt tag How to use Google Sheets to VLOOKUP data from another workbook Copy URL
  1. 6. Close quotation marks and then open again to include the range_string. Here, you need to include the sheet name followed by an exclamation point and cell range (“MAIN!$A$2:$B$101”) and set the dollar sign before the cell index to prevent the cell contents from undergoing any changes.
How to use Google Sheets to VLOOKUP data from another workbook Range string

How to Import CSV to Google Sheets Automatically?

If you need to open CSV files in Google Sheets, you can either do it manually or automatically. Here's how to import CSV files into Google Sheets.

READ MORE
How to Import CSV to Google Sheets Automatically
  1. 7. Close parenthesis to complete the IMPORTRANGE syntax, include a comma, and then type in the index. In this case, it’s the number of the ‘Company’ column in the other spreadsheet (2).
How to use Google Sheets to VLOOKUP data from another workbook Column index
  1. 8. Once you include the index, you then include the [is_sorted] argument by typing in ‘false’, as we want the exact match. Press Enter.
How to use Google Sheets to VLOOKUP data from another workbook REF
  1. 9. It is common to get a “#REF” if you forgot to share or do not have permission access to the other worksheet. If you are not given the option to “Allow access” when clicking on “REF”, then make sure that the file is saved as a Google Sheet and not as a Microsoft Excel Spreadsheet.
How to use Google Sheets to VLOOKUP data from another workbook Save as Excel
  1. 10. Once saved as a Google Sheet, you can press Enter and you will be given the option to “Allow Access”.
How to use Google Sheets to VLOOKUP data from another workbook Allow Access
  1. 11. When allowed, the VLOOKUP and IMPORTRANGE formulas work and return the value you were trying to import in the C2 cell.
How to use Google Sheets to VLOOKUP data from another workbook Single cell result
  1. 12. To apply the formulas to the remaining cells, simply drag them down from the bottom-right corner of the cell.
How to use Google Sheets to VLOOKUP data from another workbook Drag down

How to VLOOKUP between different Google Sheets with Sheetgo

Using Sheetgo, you can avoid the disadvantages of using VLOOKUP between different Google Sheets. Instantly connect your Google Sheets files to transfer the data where you need it. No need to worry about exposing sensitive data, decreasing performance, or constantly fixing broken formulas.

  1. 1. Select the source file. 
Select source file in Sheetgo
  1. 2. Select the destination file and save. 
Select the destination file in Sheetgo
  1. 3. Your destination file now has a sheet with the data from the source file.
Destination file with source data in Sheetgo
  1. 4. Below, I used VLOOKUP to get the companies corresponding to each student ID.
VLOOKUP in Sheetgo

Conclusion

You now know how to use VLOOKUP within a single Google Sheets file, as well as how to combine it with IMPORTRANGE to use it between different files. However, you also know that there are certain disadvantages to using IMPORTRANGE. Fortunately, you know about Sheetgo, a solution that allows you to instantly connect your files and avoid the limitations of performing a lookup between different files. Sign up for Sheetgo and start saving time right away.

If you’re interested in learning about more methods to transfer data within Excel files automatically, visit our blog section for Excel and check how to transfer data from one Excel worksheet to another automatically.

Use Excel as your main tool for data management? Discover how to use the Excel VLOOKUP formula to search data from another sheet.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 7 2022, Updated Sep 11 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets