Replace excel rows from another sheet where column value matches

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.

Solution 1:

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

enter image description here