Get help

Help center

Guides and answers

Contact support

Write to our team

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Finance

Turn manual finance work into reports that update themselves.

What Finance teams automate

  • Track payables and receivables
  • Build cash flow statements
  • Compare budget against actuals

Inventory and manufacturing

See stock levels and automate ordering.

What inventory and manufacturing teams automate

  • Track inventory across multiple sites
  • Connect sales, purchasing, and warehouse data
  • Link a barcode scanner to your sheet

HR

Run recruitment, attendance, and OKR tracking from your sheets.

What HR teams automate

  • Track recruitment and onboarding
  • Record team OKRs and results
  • Track check-ins, check-outs, and leave

Project management

Assign tasks and track hours without manual updates.

What project teams automate

  • Assign tasks and track status changes
  • Estimate effort and log hours worked
  • Track milestones across projects

Sales and CRM

Track leads and sales performance with automated reports.

What sales teams automate

  • Track and convert leads
  • Set sales goals and track team metrics
  • Monitor sales KPIs on dashboards

Education

Automate attendance, grades, and school purchasing.

What schools automate

  • Track attendance and notify parents
  • Record and compare class grades
  • Track inventory lent to students and staff

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Get started

Templates

Ready-made workflows to start from

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Help center

Guides and answers

Contact support

Write to our team

Workflows

Build processes with your team

Automations

Connect files and automate tasks

How to VLOOKUP from another sheet or workbook in Google Sheets

When working with large datasets, the information tends to be spread out among different spreadsheets. Google Sheets 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, an AI-powered data automation platform. 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.

VLOOKUP from another sheet (another tab in the same file)

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.

Google Sheets allows you to VLOOKUP from another sheet within the same file, or from another Google Sheets 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 column in the range that holds the value you want returned. 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. 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’.

Google Sheets VLOOKUP from another sheet — VLOOKUP suggested while typing in the Email column of STUDENT INFO

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.

Google Sheets VLOOKUP from another sheet — cell A2 with the Student ID picked as the search key

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.

Google Sheets VLOOKUP from another sheet — range A2:F101 selected on the FILE tab

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’.

Google Sheets VLOOKUP from another sheet — index 5 typed for the Email column

5. We want an exact match, so we place ‘false’ in the [is_sorted] parameter.

Google Sheets VLOOKUP from another sheet — false added as the last argument for an exact match

6. Once all the arguments are included, press Enter and it will automatically return the first value.

Google Sheets VLOOKUP from another sheet — email address returned in cell C2

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.

Google Sheets VLOOKUP from another sheet — formula dragged down the Email column

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. If you only need one cell from another tab, you can reference a cell in another sheet without VLOOKUP.

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.

VLOOKUP from another workbook (a separate file)

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‘s AI-powered automations 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: the range to import, written as the sheet name, an exclamation point, and the cell range (for example, MAIN!A2:B101). It can either be enclosed in quotation marks or appear as a referenced cell.

Let’s see it more clearly in the example.

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.

VLOOKUP from another workbook in Google Sheets — VLOOKUP typed in the new Company column

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.

VLOOKUP from another workbook in Google Sheets — cell A2 picked as the search key

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.

VLOOKUP from another workbook in Google Sheets — IMPORTRANGE in the function suggestions

4. Open quotation marks.

VLOOKUP from another workbook in Google Sheets — quotation mark opened inside IMPORTRANGE

5. Include the spreadsheet_url by copying the link from “…d/” until “/edit…”.

VLOOKUP from another workbook in Google Sheets — spreadsheet key highlighted in the INTERNSHIP INFO address bar

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.

VLOOKUP from another workbook in Google Sheets — range string MAIN!$A$2:$B$101 added to IMPORTRANGE

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).

VLOOKUP from another workbook in Google Sheets — column index 2 added after IMPORTRANGE

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.

VLOOKUP from another workbook in Google Sheets — #REF! error in cell C2 of a file still in XLSX format

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.

VLOOKUP from another workbook in Google Sheets — Save as Google Sheets in the File menu

10. Once saved as a Google Sheet, you can press Enter and you will be given the option to “Allow Access”.

VLOOKUP from another workbook in Google Sheets — Allow access button under You need to connect these sheets

11. When allowed, the VLOOKUP and IMPORTRANGE formulas work and return the value you were trying to import in the C2 cell.

VLOOKUP from another workbook in Google Sheets — company Murray-Bechtelar returned in cell C2

12. To apply the formulas to the remaining cells, simply drag them down from the bottom-right corner of the cell.

VLOOKUP from another workbook in Google Sheets — company names filled down the Company column

To fill a whole column of search keys with one formula, or to fix the errors this formula can return, see our guide to VLOOKUP with IMPORTRANGE.

How to VLOOKUP between different Google Sheets with Sheetgo

Using Sheetgo, an AI-powered data automation platform, 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. Select the source file.

VLOOKUP between Google Sheets with Sheetgo — INTERNSHIP INFO file and MAIN tab picked as the source

2. Select the destination file and save.

VLOOKUP between Google Sheets with Sheetgo — STUDENT INFO picked as the destination with a Sheetgo_MAIN tab

3. Your destination file now has a sheet with the data from the source file.

VLOOKUP between Google Sheets with Sheetgo — Sheetgo_MAIN tab added next to the MAIN, FILE and PROGRAM tabs

4. Below, I used VLOOKUP to get the companies corresponding to each student ID.

VLOOKUP between Google Sheets with Sheetgo — VLOOKUP reading the Sheetgo_MAIN tab to return company names

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, an AI-powered solution that allows you to instantly connect your files and avoid the limitations of performing a lookup between different files.

For the syntax and more examples, see our guide to the VLOOKUP function in Google Sheets. To return a value from a column to the left of your search keys, which VLOOKUP can’t do, use XLOOKUP in Google Sheets instead.

You may also like…

google sheets features and formulas

How to find nominal interest rate using Google Sheets

Sarah is a young professional making significant financial decisions. From credit cards to mortgages, student loans, and investment...
google sheets features and formulas

Pull Google Calendar data into Google Sheets using Apps Script

This post was originally published in our community forum. Objective : This guide demonstrates how to use Google Apps Script to pull data...
google sheets features and formulas

Using the PROPER function to capitalize the first letter of each word in Google Sheets

Note: This post was originally published in our community forum. Ever found yourself needing to clean up a list of names, titles, or any...