Copy data from one sheet to another based on criteria Google Sheets

When working with multiple datasets across worksheets in Google Sheets, it can get quite frustrating and time-consuming to look up data between different sheets.

Google sheets, however, provides a feature that saves you the trouble by looking up the data for you.

It looks up and retrieves matching data from another table, which could be on the same sheet or on a different sheet.

In this Google Sheets VLOOKUP From Another Sheet guide, I will show you how to use the VLOOKUP formula in Google Sheets (from the same workbook or a different workbook).

• Google Sheets VLOOKUP: What is the VLOOKUP Function in Google Sheets?
• Google Sheets VLOOKUP Syntax
• Can Google Sheets Vlookup From Another Sheet?
• How to VLOOKUP from Another Sheet in a Different Google Sheets Workbook
• Some Tips when Using VLOOKUP to Reference Another Sheet/Workbook
• Learning More About VLOOKUP Google Sheets From Another Sheet

Google Sheets VLOOKUP: What is the VLOOKUP Function in Google Sheets?

VLOOKUP stands for Vertical Lookup. It is a Google Sheets function that lets you perform a vertical lookup. In other words, it lets you search a particular lookup value down the first column of a range of cells.

Once it finds a matching value, it looks for a value in another specified column in the same row as the lookup value and retrieves it.

If this definition sounds confusing, hang in there. It will get clearer when we explain it with an example.

There is a notion among a lot of Google Sheets newbies that VLOOKUP is difficult to understand and apply.

However, this is not true.

In this tutorial, you’ll see how easy it is to apply. Once you get the hang of it you will also realize how powerful a tool it can be!

Google Sheets VLOOKUP Syntax

The syntax for VLOOKUP in Google sheets is as follows:

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

Here,

• search_key represents the unique identifier or key value that you want to look up. This can either be a value or a reference to a cell containing the value.
• range is the range of cells (in the source table) within which the VLOOKUP function should search. Always make sure that this range contains 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 is the column number within the range from which the corresponding value (the one in the same row as search_key) should be retrieved. So, 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 an optional parameter. This can either be TRUE or FALSE.

A FALSE value for is_sorted indicates that the first column of range does not need to be sorted in ascending order. So, the VLOOKUP function searches for an exact match of the search_key.

If there is more than one value that is exactly equal to search_key, then VLOOKUP accesses the first occurrence of the search_key.

A TRUE value, on the other hand, means that the first column must be sorted in ascending order.

So, 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.

By default, the parameter is_sorted is set to FALSE.

Can Google Sheets Vlookup From Another Sheet?

Maybe you know what to do when you want VLOOKUP to fetch data from the same sheet in Google Sheets. But what if the data you want to look up is in a different sheet of the same workbook?

Google Sheets can lookup in another sheet, but with a slight difference in the second parameter.

Here’s how to VLOOKUP in Google Sheets from another Sheet in the same workbook.

How to use Google Sheets VLOOKUP from Another Tab

Let us assume the Employee Table is in a sheet called ‘Employees’ to learn how to VLOOKUP Google Sheets from another tab.

…and the Sales Table is in a separate sheet called ‘Sales’.

We want to access the Employees sheet, retrieve the Hourly Rates corresponding to employee ID’s “E010” and “E014” and display them in cells B3 and B4 of the Sales sheet.

Here are the steps that you need to follow 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). For our example, click on cell B3 of the Sales sheet.
2. Type: =VLOOKUP, followed by opening parentheses. Your formula bar should now show:
   
3. Next, select the cell containing the value you want to look up. For our example, select cell A3, followed by a comma. Your formula bar should now show:
   
4. For the second parameter, select the Employees tab (to open the Employees sheet). Select the range of cells that you want VLOOKUP to search in. In our example, select cells in the 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 is done so that these references do not get changed when the formula is copied to other rows in the column. Your formula bar should now show:
   
6. Put a comma, followed by the index of the column that contains the values you want to retrieve. In our example, we want to retrieve Hourly Rates, which is the third column in the range A3:C8. So we type the number 3.
   
7. Finally, close the parentheses.
8. Press the return key and wait a while for VLOOKUP to finish processing.
9. You should now be able to see the Hourly Rate corresponding to employee ID “E010” in cell B3 of your Sales sheet.
   
10. Drag down the fill handle (located at the bottom right corner of cell B3), to copy the formula to other cells in the column.
11. You should now see Hourly Rates corresponding to every employee whose ID is mentioned in column A of the Sales sheet!
    

Explanation of the Formula

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)

Notice the exclamation mark “!” between the sheet name and cell range when we VLOOKUP in Google Sheets from another tab.

In our example, we wanted to lookup data from the range A3:C8, located in a sheet named ‘Employees’. So the formula became:

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

Also notice that there are single quotes around the sheet name. You don’t need to add the single quotes if you are using the default sheet names that Google Sheets provides, like Sheet1, Sheet2, etc.

In our case, we have given the employees sheet a user-defined name. That is why we needed to enclose its name in single quotes.

This formula fetches the value from the third column of the sheet named ‘Employees’ in the same workbook.

Using VLOOKUP in Google Sheets From Multiple Tabs

If you need to Voolup multiple sheets in 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. For instance, if the above example we went through had two tabs for employees titled Employees 1 and Employees 2, you could use the following formula to search both tabs at once. I’ve bolded the changes that we made from the original formula to make it easier to see.

=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 and IFFERRORs to avoid importing errors.

How to VLOOKUP from Another Sheet in a Different Google Sheets Workbook

Now let us see what happens when we want VLOOKUP to fetch data from a sheet in a different workbook.

Again, we use the same function but with a slight difference in the second parameter.

This time, the second parameter is going to include the IMPORTRANGE function. Heres what you need to know to learn How to Do VLOOKUP in Google Sheets From a Different Sheet using the IMPORTRANGE function.

IMPORTRANGE Function in Google Sheets

The IMPORTRANGE function is used to import values from cells in another spreadsheet into your current spreadsheet.

The syntax for the formula is as follows:

IMPORTRANGE(spreadsheet_key, range_string)

It takes two parameters:

• spreadsheet_key: This is the URL of the spreadsheet you want to import from. It should be specified in double-quotes.
• range_string: This is a reference to the range of cells that you want to import. The range_string parameter should contain the sheet name as well as the range of cells that you want. So if you want to import cells A3:C8 from a worksheet named ‘Employees’, the range_string will be “Employees!A3:C8”. Remember to specify the whole range_string within double-quotes.

Now getting back to our example.

Let us 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), retrieve the Hourly Rates corresponding to employee ID’s “E010” and “E014” and 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). For our example, 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 that you want to look up (‘Wb1’) and select the sheet tab (‘Employees’ in our example).
   
6. Copy the URL of this worksheet from the location bar of your browser.
   
7. Return to your current workbook (‘Wb2’) and paste the URL at the end of the formula in the formula bar (remembering to enclose the URL in double-quotes). Your formula bar should now look like this:
   
8. Next, 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 in the dollar signs.
10. Enclose the whole parameter (source sheet name, exclamation mark, and sheet name) in double-quotes.
    
11. Put 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, which is the third column in the range A3:C8. So we 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 a while for VLOOKUP to finish processing.
15. You will be asked to allow access to the Employees sheet in order to connect. Press the ‘Allow Access’ button.
    
16. You should now be able to 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 whose ID is mentioned in column A of the Sales sheet!
    

Explanation of the Formula

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)

Notice the sheet name, exclamation mark “!” and cell range are all enclosed within double-quotes.

In our example, we wanted to lookup data from the range A3:C8, located in a sheet named ‘Employees’ of workbook ‘Wb1’. So the formula became:

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

Note: For security purposes, Google Sheets does not allow access to sheets that you are not authorized to access. So if you want to access one workbook from another, you need to either be the creator of both workbooks or have the authorization to use it from the creator.

Can I Do a VLOOKUP Across Multiple Google Sheets?

Much like we did for the range when searching multiple criteria or tabs you can do the same thing with workbook references by encapsulating all the ranges in squiggly brackets {} and separating them with a semicolon ;. So, to add a second sheet to the above example, 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)

Some Tips when Using VLOOKUP to Reference Another Sheet/Workbook

Make Sure You Have Permissions

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

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.

Learning More About VLOOKUP Google Sheets From Another Sheet

In this tutorial we showed you how to VLOOKUP from:

• a different sheet in the same workbook
• a different sheet in a different workbook.

As you get more accustomed to using VLOOKUP to reference information from different sources, you will understand what a powerful tool VLOOKUP can be.

This is especially helpful because any change in the original sheet gets automatically updated in the connected cells, making it very easy to quickly reference another sheet in Google Sheets.

This takes a lot of hassle away, freeing you up to concentrate on the best ways to use the Google Sheets VLOOKUP feature.

So this is how you can VLOOKUP from the same sheet or from other sheets in Google Sheets.

I hope you found this Google Sheets tutorial useful!

Now that you’ve mastered every Google Sheets VLOOKUP from another sheet skill, you’ve nailed one of the hardest skills involved with the VLOOKUP function. Yet, there’s still plenty more to learn about this and other functions in Google Sheets. Check out some of the other guides below to kickstart your learning.

Other Google Sheets tutorials you may like:

• How to VLOOKUP Multiple Criteria in Google Sheets
• How to Use LOOKUP in Google Sheets (Easy Tutorial)
• How to Use the HLOOKUP Google Sheets Function [Easy Guide with Examples]
• IFS Function in Google Sheets – Test Multiple Conditions (Examples)
• How to Apply a Formula to an Entire Column in Google Sheets
• How to Use the FILTER Function in Google Sheets (Examples)
• How to Compare Two Columns in Google Sheets
• How to Use IMPORTRANGE Function in Google Sheets
• How to Get the Last Value in a Column in Google Sheets (Lookup Formula)

How do I pull data from another sheet in Google Sheets based on criteria?

How do I automatically copy data from one sheet to another in Google Spreadsheet?

How do I auto populate data in Google sheets based on another cell?

How do I copy data validation from one Google sheet to another?