Match Two Name Columns and Copy Values with an Excel VBA Macro
Question details
Create a VBA macro that compares first and last names on two worksheets and copies a related third-column value when both fields match.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The source worksheet contains first name, last name, and a value to return. The destination worksheet contains first and last names that need to be matched against the source data.
- Observed behavior
- The required result is to populate the destination value column for matching first-name and last-name pairs and identify pairs that are not found.
Confirm that Sheet1 contains the destination names in columns A and B, and that Sheet2 contains source first names, last names, and return values in columns A, B, and C. Make sure name values are consistently spelled and do not contain unintended leading or trailing spaces.
Use a Scripting.Dictionary VBA macro to match both name columns
Use this method for fast matching on large worksheets. It reads the source and destination ranges into memory instead of checking cells one by one.
The macro creates one lookup key for each source record by joining the first and last names with a separator. It then looks up the same combined key for every row in Sheet1, writes matching values to column C, and marks unmatched rows in column D.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste the following code into the module: Sub MatchNamesAndCopyValue() Dim lookup As Object Dim sourceData As Variant, destinationData As Variant Dim sourceLastRow As Long, destinationLastRow As Long Dim i As Long, lookupKey As String Set lookup = CreateObject("Scripting.Dictionary") With Worksheets("Sheet2") sourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row sourceData = .Range("A2:C" & sourceLastRow).Value End With For i = LBound(sourceData, 1) To UBound(sourceData, 1) lookupKey = Trim(sourceData(i, 1)) & "|" & Trim(sourceData(i, 2)) lookup(lookupKey) = sourceData(i, 3) Next i With Worksheets("Sheet1") destinationLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row destinationData = .Range("A2:D" & destinationLastRow).Value For i = LBound(destinationData, 1) To UBound(destinationData, 1) lookupKey = Trim(destinationData(i, 1)) & "|" & Trim(destinationData(i, 2)) If lookup.Exists(lookupKey) Then destinationData(i, 3) = lookup(lookupKey) destinationData(i, 4) = vbNullString Else destinationData(i, 3) = vbNullString destinationData(i, 4) = "Does not exist in Sheet2" End If Next i .Range("A2:D" & destinationLastRow).Value = destinationData End With End Sub
Return to Excel, press Alt+F8, select MatchNamesAndCopyValue, and click Run. Matching Sheet2 values are written to Sheet1 column C, while unmatched names are flagged in column D.
Use an INDEX and MATCH formula with two criteria
Use a formula when you prefer to review results before converting them to fixed values, or when macros are unavailable.
Match names and return source values in WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet workspace for organizing source and destination lists, running compatible VBA macros where available, or using an INDEX and MATCH formula to return values based on first-name and last-name matches.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook, and verify that the destination list is on Sheet1 and the source list is on Sheet2.
- 2. Check the name and value columns: Confirm that columns A and B contain first and last names on both sheets and that Sheet2 column C contains the value to copy.
- 3. Open the macro editor or use a worksheet formula: Use the Developer or Macro tools to open the VBA editor and add the dictionary macro, or select Sheet1 cell C2 to enter the INDEX and MATCH formula.
- 4. Run or fill the lookup: Run the macro from the Macro dialog, or fill the formula down column C to return matching source values.
Frequently Asked Questions
Why should I combine first and last names into one dictionary key?
A combined key, such as FirstName|LastName, ensures that the macro matches both fields together. Matching only a first name can return an incorrect value when multiple people share the same first name.
Where does the macro place the copied value?
The example writes the matching value to Sheet1 column C. It writes "Does not exist in Sheet2" to Sheet1 column D when no matching first-name and last-name pair is found.
What happens if Sheet2 has duplicate first and last names?
The dictionary keeps the last value loaded for a duplicate key. Remove duplicates first, or modify the macro if you need to return multiple matching values.
Can I match names without using VBA?
Yes. Enter an INDEX and MATCH formula with first-name and last-name criteria in the destination value column, fill it down, and use Paste Values if you need permanent results.
