Excel公式技术问询：跨工作表查找重复项并迁移参考表信息，匹配后补充源工作表缺失数据

Hey there, let's work through this task of matching entries between your source and reference sheets, then pulling over the missing data from reference into source. I've got two solid approaches for you—pick the one that fits your comfort level:

Method 1: Use Built-in Excel Functions (Great for Non-Coders) #

First, make sure you have a unique identifier column (like an ID number or email) in both sheets—this is how we'll match rows. Let's assume:

• source sheet: Column A is the unique ID, Columns B+ have missing data you need to fill
• reference sheet: Column A is the same unique ID, Columns B+ have the complete data

Option A: XLOOKUP (Excel 365/2021+) #

XLOOKUP is more flexible than older functions. In the first empty cell of your source sheet (e.g., B2), paste this formula:

=XLOOKUP(A2, reference!A:A, reference!B:B, "", 0)

Breakdown of the parameters:

• A2: The unique ID from the current row in source
• reference!A:A: The column of unique IDs in the reference sheet
• reference!B:B: The column of data you want to pull into source
• "": What to show if no match is found (you can change this to "No match" if you prefer)
• 0: Enforces an exact match

Drag the fill handle down to apply this to all rows in source. Repeat for any other columns you need to fill (just swap reference!B:B with the target column, like reference!C:C).

Option B: VLOOKUP (Older Excel Versions) #

If you don't have XLOOKUP, use VLOOKUP with IFERROR to avoid ugly #N/A errors:

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

• A2: Unique ID in source
• reference!A:B: The range containing both the unique IDs and the data you want to pull
• 2: The column number in the range that holds your target data (here, column B)
• FALSE: Exact match required

Method 2: VBA Script (For Bulk/Complex Scenarios) #

If you have tons of rows or need to pull multiple columns at once, a VBA script will save you time. Here's a customizable script:

Sub PullMissingReferenceData()
    Dim srcSheet As Worksheet, refSheet As Worksheet
    Dim srcLastRow As Long, refLastRow As Long
    Dim i As Long, j As Long
    Dim isMatch As Boolean
    
    ' Set your worksheet names (adjust if yours are different)
    Set srcSheet = ThisWorkbook.Worksheets("source")
    Set refSheet = ThisWorkbook.Worksheets("reference")
    
    ' Find the last row with data in each sheet's ID column (Column A)
    srcLastRow = srcSheet.Cells(srcSheet.Rows.Count, "A").End(xlUp).Row
    refLastRow = refSheet.Cells(refSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through every row in the source sheet (skip row 1 if it's a header)
    For i = 2 To srcLastRow
        isMatch = False
        ' Search for a matching ID in the reference sheet
        For j = 2 To refLastRow
            If srcSheet.Cells(i, "A").Value = refSheet.Cells(j, "A").Value Then
                ' Copy data from reference to source (adjust columns as needed)
                ' This copies Columns B to Z from reference to source—change 25 to your last column index
                srcSheet.Cells(i, "B").Resize(1, 25).Value = refSheet.Cells(j, "B").Resize(1, 25).Value
                isMatch = True
                Exit For ' Stop searching once a match is found
            End If
        Next j
        
        ' Mark rows with no matching ID (optional)
        If Not isMatch Then
            srcSheet.Cells(i, "B").Value = "No matching entry in reference"
        End If
    Next i
    
    MsgBox "Data transfer complete!", vbInformation
End Sub

How to Use This Script: #

1. Press Alt + F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste the code above
4. Adjust the column ranges (e.g., if you only need Columns B to D, change 25 to 3)
5. Press F5 to run the script, or assign it to a button for easy access

Key Notes: #

• Always back up your workbook before running VBA scripts!
• If you have multiple matching entries in reference, this script will pull the first one it finds. Let me know if you need to handle duplicates differently.

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