Merge multiple Excel tables into one table based on the same columns

Key points of this article

The fields are the same but are stacked vertically when the number of rows increases; when the fields are different and correspond to the same number, they are matched according to the key column.
Before merging, make sure the key columns have the same format in each table. Leading and trailing spaces and text numbers are common causes of failure.
When one number cannot distinguish each row, use the auxiliary key column to combine multiple columns into one value and then match.
After merging, check the number of rows, unfound values and randomly sampled rows, confirm that they are correct, and then convert the formula results into numerical values and save them.

If you have three, four or more Excel sheets in your hand, each of which records different information about the same group of people or products, such as the department, salary and attendance corresponding to the employee number, you will most likely need to put them together into a complete sheet. It is easy to make wrong lines when copying and pasting directly. When there are too many lines, it is almost impossible to make mistakes during manual alignment.

The key to this type of requirement is "by what alignment". Use Excel to merge multiple tables based on the same data. The essence is to find a column of identifiers common to each table, such as serial number, order number or date plus store, and match the information in other tables row by row. This article talks about how to merge three or more tables by one or more same columns, and explains the difference between it and simple vertical stacking. After reading, you can build a merge table yourself and check whether the results are aligned.

The following method uses Excel's general functions as an example. XLOOKUP requires a newer version, and older versions can be replaced with VLOOKUP. If the table is originally embedded in PDF, it can be converted to Excel first and then processed.

How to merge multiple tables? One sentence conclusion

First look at whether each table has "the same fields but different numbers of rows" or "different fields and corresponds to the same number". The former stacks vertically and the latter matches by key column.

your situation	Recommended practices	reason
The column names and meanings of several tables are exactly the same, except for the month or department.	Stack vertically, connecting all rows under one table	There is no content that needs to be aligned, just append the line directly
Each table has the same number column, and other fields need to be added to the main table	Based on the main table, use XLOOKUP to get values column-by-column by number.	Aligned by number, each row of results can be traced back to the source table
A single number is not enough to distinguish each row, such as "Date + Store"	Add auxiliary key columns to each table and join multiple columns before matching.	Auxiliary keys turn multiple columns into a comparable value
The number appears repeatedly in a table	Remove duplicates or summarize first, then do matching	Duplication will cause one row of main table records to correspond to multiple rows of results, and the number of rows will not match.
The table was originally embedded in the PDF	First export the PDF to Excel, and then merge it according to the above method	What is needed before merging is an editable data table

Before you start: Confirm these points first

Taking a few minutes to check before you start can save a lot of rework.

Determine the main table. The main table is the table that will ultimately retain all rows, and information from other tables will be matched to it. If you select the wrong main table, the result will be missing rows or extra blank rows.
Confirm that the key columns have consistent meanings in each table. The column also called "number" may be employee number in one table, and job number plus department code in another table. When the content is inconsistent, the matched numbers may appear to be correct, but in fact they are not accurate.
Check data format. Textual numbers (such as "1001") and real numbers (1001) are not considered equal in Excel. Dates may also be in date format in one table and text in another table.
Check spaces and case. An extra space at the end, or the difference between full-width and half-width, will cause the match to fail, which is almost invisible to the naked eye.
Keep the original table backup. The merging process will add new columns and formulas. It is recommended to save it as a new file before operating. The original data will be retained as a basis for verification.

Steps to merge three tables by one column number in Excel

Take the main table A as an employee number, the A column in table 2 as the same number, and the C column as a department as an example:

Select the main table and mark the key columns. Confirm that the main table number is in column A, and Table 2 and Table 3 also have corresponding number columns.
Unify the key column format. Set the number columns of each table to the same format, use search and replace to remove excess spaces, and use the TRIM function to clean up if necessary.
Add a new column to the right of the main table and write the matching formula. For example, enter =XLOOKUP($A2,表 2!$A:$A,表 2!$C:$C,"未找到") in D2. Replace "Table 2" with the actual worksheet name.
Fill the formula downward. Drag or double-click the fill handle of D2 to apply the formula of D2 to the last row of the main table.
Repeat the same operation for Table 3. Place the result in column E, refer to the numbered column of Table 3 and the column to be retrieved in the formula, do not misuse the range of Table 2.
Filter "not found" and check them one by one. Some records are really missing, and some are false missing caused by format or spaces, which need to be processed separately.
Save the result after fixing it. After confirming that it is correct, select the formula column to copy, then use "Paste Special as Value" to replace the original formula to avoid the result changing after the source table is modified, and finally save it as a new file.

If your version of Excel does not support XLOOKUP, you can replace the formula in step 3 with =VLOOKUP($A2,表 2!$A:$C,3,FALSE). The 3 here represents the third column of the area from A to C, that is, the column where the department is located, and FALSE represents an exact match.

How to merge two tables based on multiple identical data?

When one number is not enough to distinguish each row, for example, the same employee has records in different months, you need to use multiple columns as keys. The method is to add a column of auxiliary keys to each table and splice the key columns into one value:

Enter =A2&"|"&B2 in the blank columns of the main and source tables, where column A is the first key column and column B is the second key column.
Use delimiters such as "|" to avoid ambiguity after splicing "1" and "11".
Each concatenated column must have a uniform format. It is recommended that dates be converted to text or the same date format.
Afterwards, match according to the auxiliary key column. The formula is the same as in single column, except that the search value is replaced by the auxiliary key column.

When there are more than three tables, each table must have an auxiliary key with the same structure, otherwise the matching results cannot correspond.

📊 Core comparison

Contrast Dimensions	stack vertically	Merge by key column
Applicable situations	The fields are exactly the same, and the content is an append relationship.	The fields are different and the content is a supplementary relationship
Number of result rows	Equal to the sum of the number of rows in each table	Usually equal to the number of rows in the main table, provided that the key columns of the source table are not repeated
Align by	No alignment required, continue according to position	Correspond row by row according to the value of the key column
Common mistakes	Column order is inconsistent, resulting in data misalignment	Inconsistent formatting or spacing resulting in lots of "not found"

Check each item after completion

After the merger is completed, do not deliver directly, at least check the following items:

Check items	How to see	What to do if there is a problem
number of lines	The number of rows in the merged main table should be consistent with the original main table	The number of rows increases, indicating that there are duplicates in key columns of the source table. Deduplication or summarization is required first and then matching.
value not found	Filter cells for "Not Found" or #N/A	Check formatting and spacing; if something is still missing, note the reason instead of filling it in randomly.
Sampling control	Randomly select 5 rows and return to the source table for manual comparison.	In case of inconsistency, check whether the area referenced by the formula is misplaced, for example, whether there is a missing dollar sign.
Numbers and dates	See if it turns into a string of numbers or the date is messed up	Re-match after unifying the format, and confirm that the display is consistent with the actual value
Is the formula fixed?	Click on the cell to see if it is still a formula	When it needs to be archived or sent, convert it to a numerical value to avoid changes in the results after the source table changes.

❓ Frequently Asked Questions (FAQ)

1. A large number of #N/A appear after the merger. What’s going on?

In most cases, the key column format is inconsistent or has extra spaces. First use TRIM to clean up the spaces, then convert the text numbers into numbers, and then match them again. Only when it is confirmed that there is still no corresponding record, it means that the source table is indeed lacking data.

2. The same number appears multiple times in the source table, how to merge it?

By default, the matching function only takes the first record, and other records will be ignored. Before merging, count duplicates and use COUNTIF to determine which numbers are duplicates; if you need to retain all the information, you should summarize it first (such as summing, taking a new one), and then do matching.

3. The key column names of the three tables are different. Do I need to change the name?

unnecessary. The column names are just headers, and the matching looks at the cell contents. Just specify the key columns and value columns of each table in the formula, as long as the content represents the same object.

4. The table is in PDF, how to convert it to Excel first?

You can use the PDF editor of the OmniDoc desktop client (download.omnidoc.top) to export PDF to Excel. The scanned PDF needs to be OCR recognized before exporting. After exporting, you still need to check whether the numbers and columns are aligned before merging.

📝 Summary

Use Excel to merge multiple tables based on the same data. The key is to first determine whether to stack vertically or match by key columns, and then unify the format of the key columns. After setting up the formula according to the above steps, and doing three checks using the number of rows, unfound values and sampling control, the merged table will be more reliable.

Open OmniDoc and work with your spreadsheet file