跨工作簿及工作表VLOOKUP匹配并更新数据需求求助

Got it, let's break down how to solve this problem. You need to cross-reference two workbooks—with the twist that one has multiple sheets—and populate values based on a two-column match. Here are two solid solutions depending on your comfort level with tools:

Power Query is ideal here because it handles multiple sheets seamlessly and lets you avoid manual work. Here's the step-by-step:

• Step 1: Prep the Master File data
  Open your Master File, navigate to the sheet with your data. Select any cell in the data range, then go to Data > From Table/Range (check "My table has headers" if your data has them). In the Power Query Editor, delete all columns except A, E, F—rename them to Master_A, Master_E, Master_F to avoid confusion. Close and load this as a Connection Only (via Home > Close & Load To > Only Create Connection).

• Step 2: Import Workbook2's multiple sheets
  Open Workbook2, go to Data > Get Data > From File > From Excel Workbook. Select Workbook2 itself, then in the Navigator, check "Select multiple items" and pick all sheets you need to process. Click "Transform Data" to open the editor, then use the "Combine & Edit" option to merge all sheets into one table. Rename columns B and C to WB2_B, WB2_C, and keep the Source.Name column (this tracks which sheet each row came from).

• Step 3: Match and merge the data
  In the Workbook2 query, go to Home > Merge Queries > Merge Queries as New. For the first table (Workbook2), select WB2_B and WB2_C (hold Ctrl to multi-select). For the second table, pick the Master File connection you created, then select Master_A and Master_F (match the order: WB2_B ↔ Master_A, WB2_C ↔ Master_F). Set the join type to Left Outer (keeps all Workbook2 rows even if no match exists). Click OK, then expand the merged column and only select Master_E.

• Step 4: Split data back to original sheets
  Now you have a combined table with all rows, their sheet names, and matched Master_E values. Go to Transform > Split Table and choose to split by the Source.Name column. This creates a separate query for each sheet. For each query, go to Home > Close & Load To and select the corresponding sheet in Workbook2, choosing "Overwrite existing cells" to populate the last column.

If you prefer a one-click automation, this VBA script will loop through every sheet in Workbook2, check for matches, and populate the last column automatically.

• How to use:

  1. Open both the Master File and Workbook2.
  2. In Workbook2, press Alt + F11 to open the VBA Editor.
  3. Insert a new module via Insert > Module.
  4. Paste this code (update the file/sheet names to match yours):

     Sub PopulateMasterE()
         Dim masterWB As Workbook
         Dim targetWB As Workbook
         Dim masterWS As Worksheet
         Dim targetWS As Worksheet
         Dim masterLastRow As Long
         Dim targetLastRow As Long
         Dim lastCol As Integer
         Dim i As Long, j As Long
         
         ' Update these to match your file/sheet names
         Set masterWB = Workbooks("Master File.xlsx")
         Set masterWS = masterWB.Sheets("Sheet1")
         Set targetWB = ThisWorkbook ' This refers to Workbook2
         
         ' Turn off screen updating for speed (optional but recommended for large datasets)
         Application.ScreenUpdating = False
         
         masterLastRow = masterWS.Cells(masterWS.Rows.Count, "A").End(xlUp).Row
         
         ' Loop through every sheet in Workbook2
         For Each targetWS In targetWB.Sheets
             targetLastRow = targetWS.Cells(targetWS.Rows.Count, "B").End(xlUp).Row
             lastCol = targetWS.Cells(1, targetWS.Columns.Count).End(xlToLeft).Column + 1
             
             ' Add a header for the new column (remove if you don't need it)
             targetWS.Cells(1, lastCol).Value = "Master_E_Value"
             
             ' Check each row for a match
             For i = 2 To targetLastRow
                 For j = 2 To masterLastRow
                     ' Exact match on B ↔ A and C ↔ F
                     If targetWS.Cells(i, "B").Value = masterWS.Cells(j, "A").Value And _
                        targetWS.Cells(i, "C").Value = masterWS.Cells(j, "F").Value Then
                         targetWS.Cells(i, lastCol).Value = masterWS.Cells(j, "E").Value
                         Exit For ' Stop checking once a match is found
                     End If
                 Next j
             Next i
         Next targetWS
         
         Application.ScreenUpdating = True
         MsgBox "Data populated successfully!", vbInformation
     End Sub
     

  5. Run the script by pressing F5 in the editor, or assign it to a button in Workbook2 for easy access.

• Pro Tips:

  • For case-insensitive matches, replace the comparison lines with UCase(targetWS.Cells(i, "B").Value) = UCase(masterWS.Cells(j, "A").Value) and same for column C/F.
  • If your data starts at row 1 (no headers), adjust the loops from 2 To ... to 1 To ... and remove the header line.

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