Buzz
In the Excel journey, there arise moments when you need to sift through data from one sheet to another for crafting insightful data reports. If the art of data extraction eludes you, discover diverse methods in the article below to fetch data from this sheet to another based on specified conditions.
Here, Mytour unfolds two techniques to pluck data from this sheet to another based on conditions. Dive into the methods and enhance your Excel prowess.
Utilizing Advanced Filter to Fetch Data from This Sheet to Another Based on Conditions
Assume you possess a data table on Sheet1 as follows:
You wish to extract data from Sheet1 to Sheet2 with conditions specified in A1:B2 on Sheet2.
Execute the following steps:
Step 1: On Sheet2, choose Data -> Advanced.
Step 2: In the Advanced Filter window, under the Action section, choose Copy to another location.
- List range: the data range to be filtered (data table in Sheet1).
- Criteria range: the filtering conditions range (in Sheet2).
- Copy to: the location in Sheet 2 where the results will be placed after extracting data from Sheet1.
- Unique records only: only fetch unique values (appear once).
Follow these steps:
Place the cursor in the List range section, and select the icon as shown below:
Next, select the name Sheet1.
Then, drag to choose the data range to be filtered in Sheet1.
Finally, click the icon below to open the Advanced Filter dialog box.
Place the mouse pointer in the Criteria range cell and drag to select the conditions in Sheet2.
Continue by placing the mouse pointer in the Copy to cell and select the cell where you want to place the results after extracting data based on conditions from Sheet1, then press OK to filter.
The results will be returned starting from the cell in Copy to that you just selected.
Note: Setting conditions in Advanced Filter
- The condition range in Advanced Filter must follow the principle, including the header names of the relevant data fields and the condition content in that field.
- How to express relationships between conditions:
Dependent relationship (AND condition): all conditions must be satisfied.
+ Each condition will have an associated title, if in the same header column, repeat that header column.
+ Conditions are placed on the same row.
Additional relationship (OR condition) – only one of the conditions needs to be satisfied.
+ Each condition will have an associated title, if in the same header column, place it in the row immediately below that header column.
+ Conditions are placed independently, not on the same row.
How to extract data from this sheet to another based on conditions using the Vlookup function
Assume you have a score data table in Sheet1:
In Sheet2, you extract student names, math scores, literature scores, and English scores from Sheet1 based on the candidate number in cell A2.
Recall the syntax of the VLOOKUP function:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In this formula:
- A2 is the lookup_value (the value to be searched).
- Sheet1!$A$1:$E$9 is the data table to be searched in Sheet1.
- 2 is the column to return (student names).
How to write the formula:
1. Enter =VLOOKUP(
2. Select cell A2 and input the comma , symbol.
3. Choose the name of Sheet1.
4. Drag to select the data table to be searched and press F4 to lock the data table.
5. Enter 2, input the closing parenthesis ) , and then press Enter.
Here are the results you obtain:
Similarly, input the VLOOKUP function for the Math, Literature, and English grades. The col_index_num for these cells is 3, 4, and 5, respectively. In this way, you will get:
By changing the student ID, you can dynamically update the corresponding Name, Math, Literature, and English grades. Compare these values with the data table in Sheet1.
Mytour has shared with you how to fetch data from one sheet to another using Advanced Filter and Vlookup. Apply the data retrieval method that suits your processing requirements. Wishing you success!
