Excel：在两表中查找重复名称并关联显示对应列数据

Hey there! Let's tackle this Excel problem you're having—finding duplicate names across two tables and pulling their corresponding string values. I get why pivot tables and basic INDEX-MATCH might not have worked perfectly, so let's go through a couple of reliable methods that should do the trick.

This function is way more straightforward than the old INDEX-MATCH combo, especially for exact matches. Let's assume:

• Your first table is in Sheet1, with names in column A and strings in column B

• Your second table is in Sheet2, with names in column A and strings in column B

• Add a new column (say column C) in Sheet1, then in cell C2 enter this formula:

  =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "No match", 0)
  

  Breakdown:

  • A2: The name we're checking in the current row
  • Sheet2!A:A: The column of names in your second table
  • Sheet2!B:B: The column of strings we want to pull if there's a match
  • "No match": The text that shows up if the name isn't found in Sheet2
  • 0: Forces an exact match (critical for avoiding partial matches)

• Drag the formula down to fill the rest of the column. Any row with a duplicate name will show the corresponding string from Sheet2; non-matches will display "No match".

• To check duplicates the other way around (from Sheet2 to Sheet1), just swap the sheet references in the formula.

If you're using an older Excel version without XLOOKUP, you can fix your INDEX-MATCH setup with this exact-match formula. In Sheet1 cell C2:

=IFERROR(INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0)), "No match")

• The MATCH function finds the row number of the duplicate name in Sheet2's name column
• INDEX pulls the corresponding string from Sheet2's B column
• IFERROR wraps the whole thing to replace annoying #N/A errors with a clean "No match" message
• Drag the formula down to apply it to all rows.

If you're working with a ton of data, Power Query is the most efficient option—it's automated, easy to refresh, and avoids manual formula dragging. Here's how:

1. Select your first table in Sheet1, go to the Data tab → click From Table/Range (make sure your table has headers). This opens the Power Query Editor.
2. Repeat step 1 to import your second table from Sheet2 into Power Query.
3. In the Power Query Editor, select your first table, then click Merge Queries → Merge Queries as New Query.
4. In the merge window:
   • Choose the "Name" column from your first table as the merge key
   • Select your second table from the dropdown
   • Choose the "Name" column from the second table as the matching key
   • Set the merge type to Inner (this only keeps rows where the name exists in both tables—exactly the duplicates you want)
5. Click OK, then click the expand arrow on the new merged column. Check the box for the string column from your second table and click OK.
6. Click Close & Load to export the results to a new Excel sheet—you'll have a clean table with only duplicate names and their corresponding strings from both tables.

If you just want a quick visual of which names are duplicated across both tables before pulling strings:

• Select all name columns from both sheets (e.g., Sheet1!A:A and Sheet2!A:A)
• Go to the Home tab → Conditional Formatting → Highlight Cells Rules → Duplicate Values
• Pick a formatting style (like red fill) and click OK. All duplicate names will be highlighted instantly.

内容的提问来源于stack exchange，提问作者show000