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 .

1. List range: the range of data to filter (the data sheet in Sheet1).
2. Criteria range: area of ​​the filter condition (in Sheet2).
3. Copy to: position in Sheet 2, where to place the result after retrieving data from Sheet1.
4. 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:

1. Lookup_value: is the value to search, is a required parameter.
2. Table_array: the data area to search for values, note the lookup value must always be in the first column of table_array.
3. Col_index_num: number of columns containing the value to return, the first column counted as 1.
4. 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:

1. A2 is lookup_value (lookup value).
2. Sheet1! $ A $ 1: $ E $ 9 is the data sheet to search in Sheet1.
3. 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!