VBA & Macro Problems

How to Update an Excel Table Using Two Matching Criteria with VBA

Rana GarciaRana Garcia Nov 10, 2026 869 views

Question details

The user needs to synchronize data between a source worksheet and a tracking table using VBA by matching two specific criteria.

How to Update an Excel Table Using Two Matching Criteria with VBA
Product
Excel
Device & OS
not provided
Scenario
Synchronizing a 'Tracker' table with a 'My Plan' worksheet where both the selected plan name and a unique plan ID must match to copy over the baseline date and completion percentage.
Observed behavior
The user requires a reliable VBA macro to loop through table rows, compare the two designated criteria against the source sheet, and insert the corresponding data into specific columns without altering incorrect ranges.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Developer tab settings to allow the VBA code to run properly.

Solution 1Recommended

Use a VBA Nested Loop to Synchronize Data

Implement a VBA macro that loops through the target table and the source worksheet to match both the Plan Name and the Unique Plan ID before updating the columns.

This solution relies on named ranges and exact column headers. Using explicit references to tables and list columns prevents the macro from accidentally modifying the wrong cells, even if columns are moved later.

For very large datasets with thousands of rows, using a nested loop may take slightly longer. However, for standard project tracking, it provides clear, reliable synchronization.

1
Access the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. From the top menu, select Insert > Module to create a new blank module.

2
Insert the Synchronization Macro

Copy and paste the following code into the module window: Sub UpdateTrackerFromMyPlan() Dim wsPlan As Worksheet, wsTracker As Worksheet Dim tbl As ListObject Dim planName As String Dim lastRowPlan As Long Dim i As Long, r As ListRow Dim planID As Variant, baselineDate As Variant, percentComplete As Variant Set wsPlan = ThisWorkbook.Sheets("My Plan") Set wsTracker = ThisWorkbook.Sheets("Tracker") Set tbl = wsTracker.ListObjects("Table1") planName = wsPlan.Range("PlanName").Value lastRowPlan = wsPlan.Cells(wsPlan.Rows.Count, "D").End(xlUp).Row For Each r In tbl.ListRows If r.Range(1, tbl.ListColumns("Workstream / Project").Index).Value = planName Then For i = 2 To lastRowPlan planID = wsPlan.Cells(i, "D").Value If planID = r.Range(1, tbl.ListColumns("Plan Unique ID").Index).Value Then baselineDate = wsPlan.Cells(i, "I").Value percentComplete = wsPlan.Cells(i, "L").Value r.Range(1, tbl.ListColumns("Baseline Due Date").Index).Value = baselineDate r.Range(1, tbl.ListColumns("Percentage Complete %").Index).Value = percentComplete Exit For End If Next i End If Next r MsgBox "Tracker updated successfully!", vbInformation End Sub

3
Run the Macro

Close the VBA editor and return to Excel. Press Alt + F8 to open the Macro dialog box, select 'UpdateTrackerFromMyPlan', and click 'Run'. The Baseline Due Date and Percentage Complete columns will instantly update based on the matching criteria.

Use a VBA Nested Loop to Synchronize Data
Verify Table and Sheet Names: Make sure your target sheet is named 'Tracker', your source sheet is 'My Plan', and your target table is named 'Table1'. Modify these names in the code if your actual workbook uses different naming conventions.
Seamless Data Automation

Manage Complex Data and Macros with WPS Office

WPS Office Spreadsheets provides powerful data handling, table management, and advanced features for automating your workflows. Whether you're working with complex formulas or extensive datasets, WPS offers a smooth and intuitive environment.

  1. 1. Download and Install WPS Office: Visit the official WPS Office website to download the free installation package and install the suite on your device.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your Macro-Enabled Workbook containing the Tracker and My Plan sheets.
  3. 3. Enable Advanced Tools: Navigate to the Developer tab (if enabled in your edition) to manage macros, or utilize WPS Office's built-in data consolidation and VLOOKUP functions as robust alternatives to scripts.
  4. 4. Run the Automation: Execute your data synchronization directly within the application to instantly update your tracking tables.
Highly compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv).Lightweight architecture ensures fast performance even with heavy datasets.Familiar user interface makes transitioning and finding tools effortless.Robust built-in tools for data synchronization, filtering, and table management.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro returning a 'Subscript out of range' error?

This error usually occurs when the macro cannot find the specified worksheet or table name. Double-check that your sheets are exactly named 'My Plan' and 'Tracker', and verify that your table is named 'Table1' via the Table Design tab.

How can I speed up the macro if I have thousands of rows?

For large datasets, nested loops can cause performance issues. You can optimize the code by reading the worksheet ranges into VBA memory arrays first, or by using a 'Scripting.Dictionary' object to store and quickly look up the unique matching keys.

Can I match three criteria using this VBA method?

Yes. To match three criteria, simply add another 'If' statement inside your loop that compares the third criteria column from the source sheet against the corresponding ListColumn in your target table.

Why are the updated date cells showing as numbers instead of dates?

Excel stores dates as serial numbers. If the target column isn't formatted correctly, the macro will copy the raw number. Select the 'Baseline Due Date' column in your Tracker table and format it as 'Short Date' via the Home tab.