How to get data from one sheet to another depends on the condition
In working with Excel, sometimes you need to filter data from one sheet to another to create data reports. If you do not know how to retrieve data, so please refer to the ways to retrieve data from one sheet to another according to the conditions in the article below.
How to get data from one sheet to another depends on the condition Picture 1
Here TipsMake.com share to you two ways to get data from one Sheet to another Sheet according to the conditions, please join in.
How to get data from one sheet to another according to the conditions of using Advanced Filter
Suppose you have the data sheet in Sheet1 as follows:
How to get data from one sheet to another depends on the condition Picture 2
You want to get data from Sheet1 to Sheet2 with the condition in A1: B2 of Sheet2.
How to get data from one sheet to another depends on the condition Picture 3
You do the following:
Step 1: In Sheet2, select Data -> Advanced .
How to get data from one sheet to another depends on the condition Picture 4
Step 2: The Advanced Filter window appears , in the Action section, select Copy to another location .
- List range: the range of data to filter (the data sheet in Sheet1).
- Criteria range: area of the filter condition (in Sheet2).
- Copy to: position in Sheet 2, where to place the result after retrieving data from Sheet1.
- Unique records only: only take values that do not overlap (appear 1 time).
How to get data from one sheet to another depends on the condition Picture 5
You do the following:
Place your mouse in the List range section , select the icon as shown below:
How to get data from one sheet to another depends on the condition Picture 6
Next choose the name Sheet1 .
How to get data from one sheet to another depends on the condition Picture 7
Then drag and select the range to be filtered in Sheet1 .
How to get data from one sheet to another depends on the condition Picture 8
Finally, click the icon as shown below to open the Advanced Filter box .
How to get data from one sheet to another depends on the condition Picture 9
Place the cursor in the Criteria range box and drag the selection to the condition in Sheet2.
How to get data from one sheet to another depends on the condition Picture 10
Continue to place the cursor in the Copy to box and select the cell to which you want to put the result after you retrieve the conditional data from Sheet1, then click OK to filter.
How to get data from one sheet to another depends on the condition Picture 11
The result will be returned starting from the cell in the Copy to you have selected.
How to get data from one sheet to another depends on the condition Picture 12
Note: How to set conditions in the Advanced Filter
- The condition area in the Advanced Filter must follow the principle of including the title of the relevant field and the content of the condition in that field.
- How to write the relationship between conditions:
Dependent relationship (conditional and): all conditions are met.
+ Each condition will include a title, if in the same column title, repeat that title column.
+ Conditions are placed on the same row.
How to get data from one sheet to another depends on the condition Picture 13
Supplementary relationship (conditional or) - only one of the conditions must be met.
+ Each condition will be accompanied by a title, if in the same title column, put in the bottom row on the same header column.
+ Conditions are staggered, not in the same row.
How to get data from one sheet to another depends on the condition Picture 14
How to get data from one sheet to another according to the conditions using the Vlookup function
Suppose you have a test score sheet in Sheet1:
How to get data from one sheet to another depends on the condition Picture 15
In Sheet2, you get the data of student's name, test scores, writing, and English from Sheet1 based on the number of students listed in cell A2.
How to get data from one sheet to another depends on the condition Picture 16
Recall the VLOOKUP function syntax:
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Inside:
- Lookup_value: is the value to search, is a required parameter.
- Table_array: the data area to search for values, note the lookup value must always be in the first column of table_array.
- Col_index_num: number of columns containing the value to return, the first column counted as 1.
- Range_lookup: search type, range_lookup = 1 is equivalent to True value -> relative search, range_lookup = 0 absolute search is equivalent to false value.
Now let's get started on how to get data from sheet1 to sheet2 through the example above.
In cell B2, enter the formula = VLOOKUP (A2, Sheet1! $ A $ 1: $ E $ 9.2)
In this formula:
- A2 is lookup_value (lookup value).
- Sheet1! $ A $ 1: $ E $ 9 is the data sheet to search in Sheet1.
- 2 is the return column (student's name).
How to write a formula:
1. Enter = VLOOKUP (
2. Select cell A2 and enter the sign ,
3. Select the name of Sheet1
4. Drag the selection of the lookup data table area and press F4 to freeze the lookup data table.
5. Enter 2 and enter a ) and then press Enter .
Your results are as follows:
How to get data from one sheet to another depends on the condition Picture 17
Similarly, you enter the VLOOKUP formula for the Math, Literature, and English Points cells, where the number of col_index_num columns returned are 3; 4; 5. So you will be:
How to get data from one sheet to another depends on the condition Picture 18
So you just need to change the number of student registration, then the student name, Math Score, Literature, English Score of that number will be changed. You can compare with the data sheet in Sheet1.
How to get data from one sheet to another depends on the condition Picture 19
Above TipsMake.com has shared with you how to get data from one sheet to another according to the conditions of using Advanced Filter and using Vlookup. You can apply data retrieval appropriate to your processing requirements. Good luck!
You should read it
- How to display sheet tabs in Excel
- How to lock Excel Sheet, set a password for the sheet in Excel
- How to hide the Sheet Tabs (sheet bar) in Excel
- How to hide and hide sheets in Excel and show them again
- How to create a password protection for Excel sheets
- Instructions for copying or moving Worksheet in Excel 2013
- How to customize Share Sheet on iPhone, iPad
- How to hide and show Sheet in Excel
- Tips to fix Google Sheet not showing protected sheet
- Tips with spreadsheets (sheet) in Excel
- Show Sheet Tab in Excel when hidden
- Part 4: Hide Sheet, Sheet tab, Scroll Bar, Workbook
May be interested
Kutools for Excel Download, install and use instructions for new users
How to copy sheet from Excel file to another Excel file
Show Sheet Tab in Excel when hidden
How to cut and split images in PDF files simply and easily
How to write mathematical formulas in Word 2010
How to insert brackets in Word, Excel square brackets, curly brackets, double quotation marks