Table of Contents
To pull data from another sheet based on criteria, you can use the VLOOKUP function. This function takes the value you want to search for as the first argument, the range to search within as the second argument, the column index of the value you want to return as the third argument, and whether you want an exact or approximate match as the fourth argument. By using this function, you can quickly and easily pull data from another sheet based on criteria.
You can use the QUERY function in Google Sheets to pull data from another sheet that meets specific criteria.
You can use the following syntax to do so:
=query(Sheet1!A1:C11, "select * where A='Mavs'", 1)
This particular example pulls data from the range A1:C11 in Sheet1 only for the rows where the value in column A is equal to Mavs.
Note: The last argument of 1 specifies that the range A1:C11 contains a header row.
The following example shows how to use this syntax in practice.
Example: Pull Data From Another Sheet Based on Criteria in Google Sheets
First, let’s enter the following dataset into the first sheet of a Google Sheets spreadsheet:
Now suppose we would like to pull each of the rows that contain “Mavs” in the Team column into Sheet2.
To do so, we can type the following formula into cell A1 in Sheet2:
=query(Sheet1!A1:C11, "select * where A='Mavs'", 1)
Once we press ENTER, all of the rows where the Team columns is equal to Mavs will be pulled into Sheet2:
Notice that all three rows where the Team column is equal to Mavs have been pulled from Sheet1 into Sheet2.
Also note that you can use the and operator within the QUERY function to pull data based on multiple criteria.
For example, we can use the following syntax to pull data from Sheet1 where the value in the Team column is equal to Mavs and the value in the Points column is greater than 20:
=query(Sheet1!A1:C11, "select * where A='Mavs' and B>20", 1)
The following screenshot shows how to use this formula in practice:
Notice that the two rows where the Team columns is equal to Mavs and the Points column is greater than 20 have been pulled from Sheet1 into Sheet2.
The following tutorials explain how to perform other common operations in Google Sheets: