There are three most practical ways to associate two worksheets in Excel, namely, using VLOOKUP function, INDEX MATCH combination, and Power Query merge query. 1. Use the VLOOKUP function to automatically bring data from another table into the main table according to a common field (such as "employee ID"). The formula is =VLOOKUP(A2, Sheet2!A:B, 2, FALSE). It is recommended to combine IFERROR to deal with no match situations; 2. The INDEX MATCH combination supports left and right searches, which is more flexible and stable. The basic formula is =INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0)); 3. Power Query is suitable for processing large amounts of data, and realizes automated associations through steps such as importing data, merging queries, expanding columns and refreshing synchronization. In addition, hyperlink jumps, defining name references and structured table references can also be used. When selecting methods, data volume, update frequency and personal proficiency should be considered.

Related two worksheets in Excel is mainly to create a linkage between data, which is convenient for searching, summarizing or updating. The most common scenario is to automatically bring information from a table into another table based on a common field (such as ID or name).

The following methods are the most practical and suitable for most users' daily use.

Simple matching using VLOOKUP function

If you have two forms, one records the basic information of the employee and the other records the employee's salary. If you want to merge the salary information into the first table, you can use VLOOKUP .

Operation steps:

• Make sure that there is a common field in both tables (such as "employee ID") and that the field is unique on each row.
• Add a new column to the main table and enter a formula similar to the following:

 =VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

in:

• A2 is the value to match in the current table;
• Sheet2!A:B is a search range in another table, note that the area must contain the data column to be returned;
• 2 means that you want to return to which column in the search area;
• FALSE means exact match.

Tip: If no match is found, #N/A will be displayed. You can wrap it with IFERROR to make the results more friendly:

 =IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "")

Use INDEX MATCH to achieve more flexible search

Although VLOOKUP is good, it has a disadvantage: you cannot check left. At this time, INDEX and MATCH can be used.

Basic formula:

 =INDEX(target column range, MATCH(find value, find column range, 0))

For example:

 =INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

The advantage of this combination is that it supports left and right searches, and its performance is more stable.

Merge two tables using Power Query (suitable for large amounts of data)

If you are processing thousands or more data, it is recommended to use Power Query to make table connections.

Operation process:

1. Import two tables into Power Query (select the Data Area → Data Tab → From Table/Region);
2. In the query editor, select the main table and click "Merge Query";
3. Select the association field, select another table, and set the connection type (usually select "Left Join");
4. Expand the newly added columns to retain the information you need;
5. Finally, click "Close and Upload" and the data will be automatically synchronized back to Excel.

The advantage of this method is that it has a high degree of automation. Once set up, you can update the data next time just by refreshing it.

A brief description of other methods

In addition to the above mainstream methods, there are some tips to achieve linking effects:

• Hyperlink Jump: If you just want to jump to another table quickly, you can insert a hyperlink in the cell to point to a specific worksheet and location.
• Define name reference: For fixed-range data, you can define names and call them directly in other tables.
• Structured references between Excel tables: If you use "table" (Ctrl T), you can directly refer to it with the field name, for example =Table2[姓名] .

These methods have their own applicable scenarios, and which one is chosen depends on your data volume, update frequency and proficiency. Basically all this is it. The things that are not complicated but are easy to ignore details are the uniqueness of the field and the locking of the area. If you are not careful, you will make mistakes.

The above is the detailed content of how to link two excel sheets. For more information, please follow other related articles on the PHP Chinese website!