OmniDocHow to match the same data between two Excel tables and find the corresponding content
To match two Excel tables with the same data, the key is to first select a column of common key fields, and then use the search function to pull the contents of the other table over. This article explains how to select key fields, how to write XLOOKUP and VLOOKUP, what to do when they cannot be found, and how to check the results.
Key points of this article
- Before matching, first find a key field with non-duplicate values in both tables, such as employee number, order number, and ID number. This determines whether the match is accurate.
- If you just want to pull the corresponding content into the same table, use XLOOKUP or VLOOKUP; if you just want to determine whether a certain record is in another table, use COUNTIF.
- The format of key fields must be consistent. Text numbers and numeric numbers look the same, but they don’t match. Extra spaces in the file will also cause them to not be found.
- Records that cannot be found will display an error value, and use the fourth parameter of IFERROR or XLOOKUP to give a prompt such as "not found" to facilitate screening.
- After the results come out, use row counting and sampling verification to confirm that there are no missing or mismatched matches.
Corresponding two Excel tables is a very common thing in daily work: one is the list, one is the results; one is the order, one is the payment record; one is the employee roster, and the other is attendance. Each of the two tables has a part of information, and it is necessary to "align" the contents of the other table according to a certain same field. Manually search line by line, the longer the table, the easier it is to make mistakes.
This article is for people who need to do this type of matching. After reading, you can determine which column to use as the key field, write a search formula to pull the corresponding content, determine which records are in both tables, and know how to check whether the results are reliable. The method used in this article is a general search function in Excel, and can also be operated with the same idea in the UniCell table of the OmniDoc desktop client.
How to match the two tables? One sentence conclusion
The core of the match is a common key field that exists in both tables, and each value is unique. After selecting, select the function based on the results you want.
| your situation | Recommended practices | reason |
|---|---|---|
| To pull the contents of one or more columns from another table to the current table | XLOOKUP (when supported by the version) or VLOOKUP | Search by key fields and return the value of the specified column, suitable for most corresponding needs |
| I just want to know if a certain record exists in another table | COUNTIF | Returns the number of occurrences. If it is greater than 0, it means it exists. There is no need to return other columns. |
| The key fields are not in the left column and VLOOKUP cannot be used. | INDEX plus MATCH combination | Values can be obtained from columns at any position, regardless of left or right position restrictions |
| Key fields in the two tables have duplicate values | Remove duplicates or add auxiliary columns first, and then match | Duplicate keys will cause the search to only retrieve the first item, making the results unreliable. |
Before you start: Confirm these points first
Before you start writing the formula, take a few minutes to confirm the following items to avoid most rework.
- Whether the key fields are really not repeated. Fields such as job number, order number, and ID number are usually not repeated; names, dates, and departments have high repetition rates and are not suitable for key fields. If you must use a name, make sure you have the same name first.
- Whether the key fields of the two tables have the same format. The number in one table is text, and the number in the other table is number. They look the same, but they don't actually match. You can select a column and observe the cell's number format, or check the length with a formula next to it.
- Whether there are extra spaces in the key fields. Data exported from the system often contains leading and trailing spaces or invisible characters, resulting in "looks the same but cannot be found". You can use the TRIM function to clean up before matching.
- Whether the two tables are placed in the same file. For different worksheets in the same workbook, the worksheet names must be clearly stated when referencing them; if they are scattered in different files, the formulas will be more complex, so it is recommended to merge them into one file first.
- Does the table come from PDF? If the data only has a PDF version, you can first use OmniDoc's PDF editor to export it to Excel before starting the matching; the scanned PDF needs to be OCRed first, and the recognition results must be checked item by item.
Steps to use XLOOKUP to pull corresponding content into the same table
Let’s take an example to illustrate: Sheet1 has the job number in column A and the name in column B. The department needs to be fetched from Sheet2. The job number in Table 2 is in column A, and the department is in column C.
- Put the two tables into the same file. Make sure that both Sheet 1 and Sheet 2 are in the current workbook, and note the worksheet name of Sheet 2, such as Sheet2.
- Write the header in the blank column of Table 1. For example, if you write "Department" in C1, this column will store the matching results.
- Enter the search formula in C2. Written as
=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"未找到"), the meaning is: take the job number of A2 in Table 1, go to column A of Table 2 to find it, and after finding it, return the value of column C of Table 2. If it cannot find it, it will display "Not Found". - If the version does not support XLOOKUP, use VLOOKUP instead. Written as
=VLOOKUP(A2,Sheet2!$A:$C,3,FALSE), where 3 means return column 3 of the search range and FALSE means an exact match. Note that VLOOKUP requires the key field to be in the left column of the lookup area. - Fill the formula down. Double-click or drag the fill handle in the lower right corner of the cell to apply the formula to all rows. Make sure that the A2 referenced by each line changes with the line number.
- Check the returned results. Randomly look at a few rows and compare them with Table 2 to confirm whether the department is correct; if you see rows with "not found" or wrong values, leave them for later processing.
- When you need to determine whether it exists, add another column COUNTIF. Enter
=COUNTIF(Sheet2!A:A,A2)in D2. If the result is greater than 0, it means that the job number exists in Table 2, and if it is equal to 0, it means that it does not exist. By filtering this column out to 0, you can find records that only appear in Table 1.
Check each item after completion
The formula calculates the result, but it does not mean that the result is correct. The following checks can help you find missed and mismatched matches.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Does the number of rows match? | Count the number of valid rows in Table 1 and compare it with the number of non-empty rows in the matching results | There are fewer rows. Check whether the formula is filled to the last row. |
| Error values and number of "not found" | Filter or count records not found | Check whether these key fields are really missing in another table, or there is a format or space issue. |
| Sampling check | Randomly select about 10 rows and manually compare the two original tables. | When the results are inconsistent, check whether the referenced column is correct and whether the absolute reference symbol is written correctly. |
| Repeat key fields | Use COUNTIF to count the number of occurrences of key fields. If it is greater than 1, it is a duplicate. | Process duplicate records first, keep the correct one, and then match again |
| Text and numbers | Compare the format of key fields in the two tables | Recalculate after uniform conversion to the same format |
If the form contains content involving contract amounts, medical data or legal terms, the matching results require manual review, and machine calculations cannot replace the verification.
📊 Core comparison
There are four common matching methods, each with different scopes of application.
| method | what can be done | Things to note |
|---|---|---|
| XLOOKUP | Return the value of any column according to the key field, and support the default prompt when it cannot be found. | Newer version support is required, older versions cannot be used |
| VLOOKUP | Returns the value of the specified column by key field, supported by almost all versions | The key field must be in the left column of the search area. If the column number is written incorrectly, an error message will be returned. |
| INDEX plus MATCH | Flexible, can take value from any position | The formula is long, and beginners can easily write the wrong range of the two parameters. |
| COUNTIF | Determine whether the record exists and count the number of occurrences | It can only determine whether it exists or not, and cannot return the contents of other columns. |
❓ Frequently Asked Questions (FAQ)
1. The formula is clearly written correctly, but why does it still display an incorrect value?
The common reason is that the format of the key fields is inconsistent, such as text numbers on one side and numeric values on the other. Followed by extra spaces. First unify the format, use TRIM to clean up the spaces, and then recalculate.
2. The key fields of both tables are names. Can they be matched directly?
Yes, but the risk is higher. People with the same name will cause the wrong records to be matched. If there is no number such as job number, it is recommended to use the name plus department, joining date, etc. to form an auxiliary column, and then do the matching.
3. How to find and mark the same data?
Use COUNTIF to determine whether it exists, and then use conditional formatting or filtering to highlight rows with results greater than 0. This allows you to visually see which records in the two tables are the same.
4. What should I do if the data cannot match if it is copied from a web page or PDF?
Content copied from web pages often contains hidden characters and extra spaces, and tables converted from PDF may also have merged cells or line breaks. Clean these contents first, remove newlines and spaces, and then match. PDF tables can be exported to Excel using a PDF editor first to reduce manual editing.
5. The two tables have a large amount of data. Will the formula be very slow?
When the amount of data is large, referencing the entire column (such as A:A) will increase the amount of calculation. You can limit the reference range to the rows of the actual data, such as Sheet2!A2:A5000, and the calculation will be faster.
📝 Summary
To match two Excel tables, first select the key fields that are common in both tables and have non-overlapping values, unify the format and clean up the spaces, then use XLOOKUP or VLOOKUP to pull the corresponding content, and use COUNTIF to determine whether it exists. After the results come out, check the number of rows, check for unfound records, and compare samples with the original table, so that the results obtained are reliable.
