How to Use Google Sheets VLOOKUP from Another Sheet

Let me explain how to use Google Sheets VLOOKUP from another sheet. This popular function saves you from having to manually switch windows to pull data. So how do you use Google Sheets VLOOKUP for data in another sheet? I’ll show you how to use it with single tabs, multiple tabs, and different documents. Read on for my full step-by-step guide.

What Is the VLOOKUP Function in Google Sheets?

VLOOKUP (short for Vertical Lookup) is a Google Sheets function that lets you search for a particular value within the first column of a range of cells. I’ve already covered the function in my video below. Here, I’ll discuss specifically how to use VLOOKUP from another sheet.

Basically, once the program finds a matching value, it looks for a value in another specified column (in the same row as the lookup value) and retrieves it. You can pull data from a different part of your spreadsheet, another tab in your workbook, or from another workbook entirely. That’s powerful!

VLOOKUP from Another Sheet: Formula Syntax

Before I introduce you to the process, it’s important to understand the syntax for VLOOKUP in Google Sheets. Here’s the formula. Below, I’ll break down its parts. First, I’ll remind you of the basic formula. Then, I’ll show you the syntax for VLOOKUP from another sheet.

Breakdown of the VLOOKUP Formula

=VLOOKUP(search_key, range, index, [is_sorted])

Inside the VLOOKUP function, you’ll notice a few requirements. Here’s what each of them mean:

• search_key: A search_key represents the unique identifier (or key value) you want to look up. This can be a value or a reference to a cell that contains the value.
• range: Range is the span of cells (in the source table) that the VLOOKUP function searches within. This range needs to include the column containing the search key, as well as the column containing the corresponding value to be retrieved. VLOOKUP always searches the first column of Range to find the search key.
• index: Index is the column number within the range from which the corresponding value (i.e., the one in the same row as search_key) is retrieved. Therefore, the first column within the range has an index of 1, the second column has an index of 2, and so on.
• is_sorted: Is_sorted is an optional parameter. This can either be TRUE or FALSE.
  • FALSE Value: A FALSE value for is_sorted indicates that the first column of Range doesn’t need to be sorted in ascending order. The VLOOKUP function searches for an exact match of the search_key. If there’s more than one value that is precisely equal to search_key, VLOOKUP accesses the first occurrence of the search_key. Note: By default, the parameter is_sorted is set to FALSE
  • TRUE Value: A TRUE value means that the first column must be sorted in ascending order. The VLOOKUP function first looks for an exact match of the search_key. If an exact match is not found, then VLOOKUP looks for the closest match.

VLOOKUP from Another Sheet: Syntax

When you pull data from another workbook or another sheet, you’ll need to use the IMPORTRANGE function. The syntax for the VLOOKUP formula (when referring to data in a different workbook) is as follows:

=vlookup(search_key, Importrange(“{sheetsURL}”,“{sheet name}!{cell range}”),index,is_sorted)

I’ll show you how to use this in my example below. This formula asks for your spreadsheet’s URL, the name of the specific sheet, and the cell range you want to collect.

How to VLOOKUP from Another Sheet

What happens when we want VLOOKUP to fetch data from a sheet in a different workbook? While we’d use the same function, there’s a slight difference. This time, the second parameter is going to include the IMPORTRANGE function.

Let’s assume the employee table is in a Workbook called ‘Wb1’ (in a sheet called ‘Employees’) and the sales table is in a separate Workbook called ‘Wb2’ (in a sheet called ‘Sales’). We want to access the Employees sheet (from workbook Wb1) and then retrieve the Hourly Rates corresponding to employee ID’s E010 and E014. We’ll display them in cells B3 and B4 of the Sales sheet (which is workbook Wb2).

Here are the steps that you need to follow:

1. Click on the first cell of your target column (where you want the VLOOKUP between sheets results to appear). In this example, I click on cell B3 of the Sales sheet of Workbook ‘Wb1’.
2. Type =VLOOKUP, followed by opening parentheses.
3. Next, select the cell containing the value you want to look up. For our example, select cell A3, followed by a comma.
4. For the second parameter, enter the function IMPORTRANGE, followed by an opening parenthesis.
5. Open the workbook you want to look up (‘Wb1’) and select the sheet tab (‘Employees’ in our example).
   
6. Copy the URL of the worksheet from your browser bar.
   
7. Return to your current workbook (‘Wb2’) and paste the URL at the end of your formula bar (remembering to enclose the URL in double-quotes). Your formula bar should now look like this:
   
8. Add a comma, followed by the sheet name of the source sheet (“Employees” in our example).
9. Add an exclamation mark (!) and type in the range of cells you want to look up from the source sheet. Remember to make these cell references absolute by adding dollar signs.
10. Enclose the whole parameter (i.e., source sheet name, exclamation mark, sheet name) in double quotations.
    
11. Add a comma followed by closing parentheses (to close the IMPORTRANGE function).
12. Put a comma followed by the index of the column containing the values you want to retrieve. In our example, we want to retrieve Hourly Rates (the third column in the range A3:C8). Therefore, we’ll type the number 3. Your formula bar should now show:
    
13. Finally, close the parentheses for the VLOOKUP function. Your formula bar should now show:
14. Press the return key and wait for VLOOKUP to finish processing.
15. If you want to access one workbook from another, you either need to be the creator of both workbooks or have authorization from the creator. To connect, you’ll be asked to provide access to the Employees sheet. Press the ‘Allow Access’ button.
    
16. You should now see the Hourly Rate corresponding to employee ID “E010” in cell B3 of your Sales sheet.
    
17. Drag down the fill handle (located at the bottom-right corner of cell B3) to copy the formula to other cells in the column.
18. You should now see Hourly Rates corresponding to every employee with an ID mentioned in column A of the Sales sheet.
    

    

Remember that your IMPORTRANGE function requires a spreadsheet key and range string. Here’s the syntax:

=IMPORTRANGE(spreadsheet_key, range_string)

• spreadsheet_key: This is the URL of the spreadsheet you’re importing from. It should be specified in double quotes (“ “).
• range_string: This references the range of cells that you want to import. The range_string parameter should contain the sheet name and the range of cells that you want. 
  • If you wanted to import cells A3:C8 from a worksheet named “Employees,” the range_string would be “Employees!A3:C8”. You’ll need to specify the whole range_string within double quotation marks.

Notice how the sheet name, exclamation mark (“!”), and cell range are all enclosed within double quotation marks. In our example below, we will want to look up data from the range A3:C8, located in a sheet named ‘Employees’ of workbook ‘Wb1’. In that case, the formula becomes:

=VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1nmaT8ggc7no8NVT9U4VXrAfvcBvvvzeJNraHx365MHc/edit#gid=0","Employees!$A$3:$C$8"),3)

How to Use Google Sheets VLOOKUP from Another Tab

Let’s assume employee data is contained in a sheet called “Employees.”

The Sales Table is in a separate sheet called “Sales.”

The mission: We want to access the Employees sheet, retrieve hourly rates of employee IDs “E010” and “E014.” Next, we want to display them in cells B3 and B4 of the Sales sheet.

To VLOOKUP from another workbook in Google Sheets:

1. Click on the first cell of your target column (where you want the VLOOKUP results to appear). In this example, I’d click on cell B3 of the Sales sheet.
2. Type: =VLOOKUP, followed by opening parentheses.
   
3. Select the cell containing the value you want to look up (in this example, cell A3) followed by a comma:
   
4. For the second parameter, select the Employees tab (which will open the Employees sheet). Select the range of cells that you want VLOOKUP to search (here, the cell range A3 to C8). Your formula bar should now show:
   
5. Press the F4 key on the keyboard to lock the cell reference range. This ensures that these references won’t change when the formula is copied to other rows in the column.
   
6. Enter a comma and the index of the column containing the values you want to retrieve. In our example, I want to retrieve Hourly Rates (the third column in the range A3:C8), so I’ll type the number 3.
   
7. Finally, close the parentheses.
8. Press the return key and wait until VLOOKUP has finished processing.
9. You should now see the Hourly Rate corresponding to employee ID “E010” in cell B3 of the Sales sheet:
   
10. Drag down the fill handle (located at the bottom-right corner of cell B3) to copy the formula into other cells in the column.
11. You should now see Hourly Rates corresponding to every employee with IDs mentioned in column A of the Sales sheet!
    

Related: How to Apply a Formula to an Entire Column in Google Sheets

Explanation of the VLOOKUP Formula I’ve Used

The syntax for the VLOOKUP formula (when referring to data in a different sheet) is as follows:

=VLOOKUP(search_key,{sheet name}!{cell range},index,is_sorted)

There’s an exclamation mark (“!”) between the sheet name and cell range when we VLOOKUP in Google Sheets from another tab.

In our example, we wanted to retrieve data from the range A3:C8, located in a sheet named “Employees.” That’s why the formula became:

=VLOOKUP(A2,'Employees'!$A$3:$C$8, 3)

Using Single Quotation Marks

Note the single quotation marks around the sheet name. You don’t need to add single quotation marks if you use the default names that Google Sheets provides (e.g., Sheet1, Sheet2). In our case, we’ve given the employees sheet user-defined names. This is why we needed to enclose the name in single quotes.

Using VLOOKUP in Google Sheets from Multiple Tabs

If you need to VLOOKUP multiple sheets within the same workbook, you can turn the range into an array by enclosing it in curl brackets { } with semi-colons; between the ranges of each sheet. Let’s break that down a bit.  If we use the above example, we have two tabs for employees (Employees 1 and Employees 2). You could use the following formula to search both tabs simultaneously:

=VLOOKUP(A2,{'Employees 1'!$A$3:$C$8; 'Employees 2'!$A$3:$C$8} , 3)

Note: You can use IF functions to pull less data from other sheets, as well as IFFERRORs to avoid importing errors.

Frequently Asked Questions about VLOOKUP from Another Sheet

Here are the most common VLOOKUP questions I hear when pulling data from anywhere besides the same spreadsheet page.

Can Google Sheets VLOOKUP from Another Sheet?

Maybe you know what to do when VLOOKUP fetches data from the same sheet. But what if the data is in a different sheet of the same workbook? Google Sheets can utilize VLOOKUP in another sheet, but there’s a slight difference in the second parameter.

Can I Do a VLOOKUP Across Multiple Google Sheets?

Like how we searched multiple criteria and tabs, you can do the same thing with workbook references. Simply encapsulate all the ranges in squiggly brackets {} and separate them with a semicolon (;). 

To add a second sheet to the above example (i.e., to VLOOKUP across multiple Sheets), it would look something like this:

=VLOOKUP(A3,{IMPORTRANGE("https://docs.google.com/spreadsheets/d/1nmaT8ggc7no8NVT9U4VXrAfvcBvvvzeJNraHx365MHc/edit#gid=0","Employees!$A$3:$C$8") ; IMPORTRANGE("https://docs.google.com/spreadsheets/d/1jsdjqwrqwsauhhhswuisss65shbbw/edit#gid=0","Employees!$A$3:$C$8")},3)

Tips when Using VLOOKUP to Reference Another Sheet or Workbook

When you’re pulling data from an entirely different workbook, you’ll want to watch out for two common issues.

1. Make Sure You Have Permissions

If you don’t have the edit permissions for the workbook you’re pulling data from, you may run into errors. You may need to ask the owner of the original spreadsheet to give you editing permissions. Alternatively, you can make a copy of the original and then VLOOKUP the copy on your own spreadsheet instead.

2. Use the Exact Range

While it may be tempting to VLOOKUP an entire row or column, this will slow the process down (and make Sheets unusable if you do it too many times). Make sure you use the exact range to VLOOKUP.

Related: How to VLOOKUP Multiple Criteria in Google Sheets

Finishing up

I hope you found this Google Sheets tutorial useful! Once you master this Google Sheets VLOOKUP from another sheet skill, you’ve nailed one of the hardest spreadsheet techniques. That said, there are plenty more Google Sheets functions that’ll take your skills to the next level!

Check out some of the other guides below to kickstart your learning.

• How to Use the HLOOKUP Google Sheets Function [Easy Guide with Examples]
• IFS Function in Google Sheets – Test Multiple Conditions (Examples)
• How to Use the FILTER Function in Google Sheets (Examples)
• How to Use IMPORTRANGE Function in Google Sheets