Table of Contents
In Excel, you can use the VLOOKUP function to pull data from another sheet based on criteria. This function looks up a value in the leftmost column of a table, and then returns a value in the same row from another column you specify. It is an incredibly powerful tool for synthesizing data from multiple sheets or sources. You can also use a combination of IF and AND functions to check multiple criteria, and the INDEX-MATCH formula to look up data from multiple columns.
You can use the Excel Advanced Filter function to pull data from one sheet into another sheet based on criteria.
The following step-by-step example shows how to use this function in practice.
Step 1: Enter Data into the First Sheet
First, let’s enter the following dataset into the first sheet of an Excel workbook:
Step 2: Define Criteria
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 define this criteria in Sheet2:
Step 3: Pull the Data from the First Sheet
Next, we can click the Data tab and then click the Advanced Filter button:
In the new window that appears, click Copy to another location and then fill in the following information:
Once you click OK, the rows from Sheet1 where the Team column is equal to “Mavs” will automatically be pulled into Sheet2:
Notice that all three rows where the Team column is equal to “Mavs” have been pulled from Sheet1 into Sheet2.