Replace excel rows from another sheet where column value matches

Question

I have 2 sheets with matching columns where the first column in each sheet contains a key. Sheet 2 contains some of the rows in sheet 1 with updated data. How can I replace the rows in sheet 1 with those in sheet 2 if there is a matching key.

Example Sheet 1

KEY	NAME	CITY
123	Bob	Seattle
124	Steven	Chicago
125	Tom	San Diego

Example Sheet 2

KEY	NAME	CITY
123	Bob Doe	New York

New Sheet 1

KEY	NAME	CITY
123	Bob Doe	New York
124	Steven	Chicago
125	Tom	San Diego

I'm not sure if this is something that can be done via VLOOKUP or if I should create a script for it.

Answer 1

Update Worksheet

Destination/Source Initial

Destination Final

In a Standard Module e.g. Module1

Option Explicit

Sub UpdateWorksheet()
    
    Const sName As String = "Sheet2"
    Const dName As String = "Sheet1"
    Const CritCol As Long = 1
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim scrg As Range: Set scrg = wb.Worksheets(sName).Range("A1").CurrentRegion
    If scrg.Rows.Count = 1 Then
        MsgBox "No data in the source worksheet.", _
            vbCritical, "Update Worksheet"
        Exit Sub
    End If
    Dim srCount As Long: srCount = scrg.Rows.Count - 1
    Dim srg As Range: Set srg = scrg.Resize(srCount).Offset(1)
    Dim sData As Variant: sData = srg.Value
    
    Dim scData As Variant
    If srCount = 1 Then
        ReDim scData(1 To 1, 1 To 1): scData(1, 1) = srg.Columns(CritCol).Value
    Else
        scData = srg.Columns(CritCol).Value
    End If
    
    Dim dcrg As Range: Set dcrg = wb.Worksheets(dName).Range("A1").CurrentRegion
    If dcrg.Rows.Count = 1 Then
        MsgBox "No data in the destination worksheet.", _
            vbCritical, "Update Worksheet"
        Exit Sub
    End If
    Dim drCount As Long: drCount = dcrg.Rows.Count - 1
    Dim drg As Range: Set drg = dcrg.Resize(drCount).Offset(1)
    Dim dData As Variant: dData = drg.Value

    Dim cCount As Long: cCount = drg.Columns.Count
    
    Dim rValue As Variant
    Dim rIndex As Variant
    Dim r As Long
    Dim c As Long
    
    For r = 1 To drCount
        rValue = dData(r, CritCol)
        If Len(rValue) > 0 Then
            rIndex = Application.Match(rValue, scData, 0)
            If IsNumeric(rIndex) Then
                For c = 1 To cCount
                    dData(r, c) = sData(rIndex, c)
                Next c
            End If
        End If
    Next r
    
    drg.Value = dData
    'drg.EntireColumn.AutoFit
    'wb.Save

    MsgBox "Worksheet updated.", vbInformation, "Update Worksheet"
            
End Sub

Answer 2

For Excel 2019 or below:

=IFERROR(INDEX(Sheet2!A2:C3,MATCH(INDEX(Sheet1!A2:C5,,1),INDEX(Sheet2!A2:C3,,1),0),{1,2,3}),Sheet1!A2:C5)

This is an array formula, so if your excel is before 2016, this has to be entered with CTRL+SHIFT+ENTER.

For Excel 365, you can do this easier to modify formula:

=LET( st, Sheet1!A2:C5,
      ct, Sheet2!A2:C3,
       skeys, INDEX( st,, 1), ckeys, INDEX( ct,, 1),
       IFERROR( INDEX(ct,MATCH( skeys, ckeys, 0 ),SEQUENCE(1,COLUMNS(st))), st )
      )

where you place your starting table from Sheet1 into the variable st and your correction table into the variable ct. It can be entered into the upper left corner of your New Sheet table and it will spill out to complete the result automatically as shown in the screenshot below.

I had to extend your table to test it, so I used:

Sheet1

Sheet2

NewSheet