3 Best Methods for Extracting Data from Another Sheet in Google Sheets

It can sometimes be challenging to extract data from one sheet to another, even for experts. This process becomes even more complex when conditions and criteria are involved. In this article, we will present three simple and effective methods for extracting data from another sheet in Google Sheets.

Method 1: Using the FILTER function to extract data from another sheet

Let’s start with the simplest method, which is using the FILTER function. This function allows you to import the entire selected range based on the given criteria.

The syntax of the FILTER function is as follows:

• range: the data range that will be included in the extraction
• condition1: the first, or only, range of criteria that the extraction will follow
• [condition2, ...]: other criteria can be added (optional)

In this example, we will extract data from the “Bookstore1” sheet to the “Booklist” sheet. Our extraction criterion is that we are only looking for hardcover books.

Follow these steps:

1. Open the “Booklist” sheet and enter the following formula in the first cell of the table:

   =FILTER(

   A prompt will appear in the cell, asking for the range and conditions.

2. Switch to the “Bookstore1” sheet and select the data range you want to extract. In our case, we have selected all the data in this table, except for the headers. Hold down the “Shift” key and drag your mouse to select the data. Press the “comma” key (,) once you have finished selecting the range.

3. Select the column or range of conditions. In our case, it is the data under the “Type” header.

4. Add a condition for checking. We are only looking for hardcovers. After the condition range, type "= "Hardback"".

5. Close the parentheses and press “Enter”. The list of books with a hardcover has been extracted to the “Booklist” sheet.

This method is simple yet effective for extracting data from another sheet based on specific criteria.

(Click on the image to enlarge)

Method 2: Using the VLOOKUP function to extract data from another sheet

The next method we will discuss is using the VLOOKUP function, one of the oldest functions used for data extraction.

The syntax of the VLOOKUP function is as follows:

• lookup_value: the value to search for
• range: the data range where the function will search for the lookup_value
• index: the column number from which the value will be returned
• [is_sorted]: indication of whether the searched column is sorted or not. This value is optional and by default, it is set to “TRUE”.

By using the VLOOKUP function, we can search a range of data in another sheet to return a conditional value from the specified column.

Follow these steps:

1. In the “Bookstore2” sheet, enter the following formula in the first cell of the “Price” column:

   =VLOOKUP(

   For the lookup_value, we will refer to the book type in cell “D4”. Press the “comma” key (,) once you have finished selecting the key.

2. Switch to the “Type Price” sheet. Here, select the data range for our lookup value.

3. Our desired return value, “Price”, is in the second column of the selected range. So our index value will be “2”.

4. Close the parentheses and press “Enter”. The price data has been successfully extracted from a different sheet.

To fill the rest of the column, you need to lock the cell references of the range; otherwise, you will get an error. Double-click on cell “E4” and press the “F4” key on your keyboard once to lock the cell reference in place. Press “Enter”. Use the fill handle to apply the formula to the rest of the column.

The VLOOKUP function is a powerful tool for extracting data from another sheet based on specific criteria.

(Click on the image to enlarge)

Method 3: Using the QUERY function to extract data from another sheet

A lesser-known yet highly useful function in Google Sheets is the QUERY function. This function allows you to filter and extract data based on a specific query.

The syntax of the QUERY function is as follows:

• data: the range of cells on which you want to perform the query
• query: the query you want to perform. The query should be placed between quotation marks (“”).
• [headers]: optional. Specifies the number of header rows above the desired data. By default, this value is set to -1.

The QUERY function goes through the designated data range and applies the given query to return the values of the corresponding rows.

Follow these steps:

1. Select the cell where you want to paste your query and enter =QUERY(.

2. Switch to the other sheet, “Bookstore2” in our case, and select the data range.

3. Enter the criteria/query. Remember to write your query between quotation marks (“”). Our query is "where D = 'Hardback'" as we are looking for lines that have the hardcover type.

4. Close the parentheses and press “Enter”.

Voila! We have successfully extracted data from another sheet using the QUERY function.

(Click on the image to enlarge)

In conclusion, these three methods will allow you to easily extract data from another sheet in Google Sheets based on specific criteria. Use them according to your needs and explore more Google Sheets features to master your data management.

Find more tips and tutorials on Crawlan.com.