How to import data from excel table to another specified excel table without copying

How to import data from an excel table into another table specified by excel, let alone copy it directly

I'll give you the formula I used to see if it works for you. For example, Table 1 and Table 2 automatically generate the data from Table 1 to Table 2.

The condition is: the top item name of the data you want to generate in Table 2 must be the same as Table 1.

=VLOOKUP($A1, Table 1! $A$2:$X$3231,8,FALSE)

$A1 means: the data in Table 2 to be checked against Table 1. (The format of column A in Table 1 and Table 2 must be the same)

Table I! $A$2:$X$3231 means: In the data area, that is, based on the data of cell $A1 in Table 2, find the sum of cell $ in Table 2 in this area of ​​Table 1 ($A$2:$X$3231) The data in A1 is the same.

8 means: When the data in Table 2 is used to find the data in Table 1, the data in column 8 corresponding to the data in cell $A1 of Table 2 will be automatically generated.

FALSE means: absolute match!

This is fine, as long as you can make one and fill in the others.

How to get data from one table in another table in EXCEL

Use the function vlookup to solve this problem;

For example, in the data in the figure below, find the "April results" with job numbers from 100 to 105 in the worksheet "Table 1" and display them in cells B2 to B7 of the worksheet "Table 2".

Enter the formula in cell B2 of "Table 2": =VLOOKUP(A2, Table 1! $A$1:$E$7,5,0)

Press the Enter key, and cell B2 will display the "April results" of job number 100 in "Table 1"; as shown in the figure below.

Here in the worksheet "Table 2", click the cursor on cell B2, then move it to the lower right corner of the cell, the cursor will show black words, click and hold, and drag it to cell B7.

Release the mouse, and cells B3 to B7 will display the "April Results" data of "Table 1". As shown below.

The job number in Table 2 mentioned above has already been written. If you enter the job number randomly, you can also follow the above operation. For example, in Table 2 in the picture below, the employee number is left blank; then, enter the formula in cell B2: =VLOOKUP(A2, Table 1! $A$1:$E$7,5,0)

After pressing the Enter key, cell B2 displays "#N/A" because the employee number has not been entered in cell A2;

Now, enter the job number, such as 102, in cell A2, and the April results for job number 102 corresponding to "Table 1" will be displayed in cell B2.

Steps to read

8

Follow steps 4 and 5 above to fill cells B3 to B7 with the formula;

Then, randomly enter the job number in A3 to A7, and the April results corresponding to the job number in "Table 1" will be displayed.

How to call another table data in EXCEL

Suppose the workbook is as shown below:

Right-click on the label "Sheet1", "Move or Copy Worksheet" - select "New Workbook" - "Create Copy" under "Workbook", as shown in the figure:

——"OK", as shown in the picture:

——Right-click on the tag-"Insert"-"OK", as shown in the figure:

Copy (or enter) the content of the first line, as shown in the figure:

Change the formula

=VLOOKUP($A2,Sheet1!$A:$D,COLUMN(),0)

Copy and paste into the "B2" cell, aim at the lower right corner, hold down the left button when the cursor becomes a " " shape, and drag to the right to the "D" column, as shown in the figure:

Then drag down, as shown in the figure:

Enter the code, the effect is as shown:

If it looks good, the formula can be changed to:

=IF(ISNA(VLOOKUP($A2,Sheet1!$A:$D,COLUMN(),0)),"＂",VLOOKUP($A2,Sheet1!$A:$D,COLUMN(),0 ))

The effect is as shown:

The above is the detailed content of How to import data from excel table to another specified excel table without copying. For more information, please follow other related articles on the PHP Chinese website!