Excel: How to Match and overwrite information by columns in Excel

Question

Excel: How to Match and overwrite information by columns in Excel

Soul 20

I can’t seem to find the right solution for needing to Match Column A in Worksheet 1 with Column A in Worksheet 2 (or even if it’s in a different workbook) and then overwrite only the matched Column A’s between the worksheet’s Column B and C of Worksheet 1 into Worksheet 2 column B and C.

Find matches of Column A’s between both sheets.

Take Sheet 1’s Column B and C and put them into Sheet 2’s ( Column A’s matched only) Columns B and C

0 comments

Answer accepted by question author

Anonymous

Hello Soul,

Thank you for your detailed explanation

Based on how I understand your requirement, there is a simple way you can try using lookup formulas. It may not be a one-click solution, but it’s a practical approach you can refer to for handling this kind of scenario.

Suppose:

In Sheet1, you store your source data (Column A, B, C) as shown

User's image

In Sheet2, you have Column A filled with IDs, and Columns B and C are the ones you want to update

User's image

In Sheet2, you can create two helper columns, for example: Column D and Column E

In cell D2 (used to update Column B), enter the formula: =XLOOKUP(A2,Sheet1!$A:$A,Sheet1!$B:$B,B2)

This works as follows: It searches for the value in A2 within Column A of Sheet1. If a match is found, it returns the corresponding value from Column B in Sheet1. If no match is found, it keeps the original value from B2 in Sheet2.

In cell E2, enter: =XLOOKUP(A2,Sheet1!$A:$A,Sheet1!$C:$C,C2)

Then drag both formulas down for all rows in your dataset.

User's image

After that, you can:

Select all values in columns D and E
Press Ctrl + C
Right-click on cell B2
Choose Paste as Values

This will overwrite Columns B and C with the updated values where matches are found. User's image

Just a small note on this approach:

I don’t place the formula directly into Columns B and C because it would either create a circular reference error (the formula would depend on the same cell it is updating), or it would immediately overwrite the existing values before they can be used as a fallback.

That’s why I use helper columns first, to safely calculate the updated results while still keeping the original data intact. Once everything looks correct, then paste the values back to overwrite the original columns.

If this approach does not fully match what you are trying to achieve, please feel free to share a bit more detail (or a sample of your data structure), and I’ll be happy to adjust the solution accordingly.

Best regards,

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Soul 20 Reputation points

2026-06-13T19:38:06.53+00:00

Thank you so much. I do think this fits exactly what I need.

The 2nd part I didn’t mention would be to separate all non matches to a separate sheet for simplified manual checking/deletion.

I am working within two systems communicating with each other and system 1 is forcing unwanted items into systems 2 excel sheet making it hard to track and edit within the system itself. Like trying to sift through 300 rows with 10 columns 😅.
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-06-13T23:17:37.7966667+00:00

Hi,

You may use the FILTER() function to accomplish this. For specific help, share some data to work with and show the expected results. Share data in a format that can be pasted in an MS Excel file.
Anonymous

2026-06-15T09:49:14.6233333+00:00

Hi Soul,

Thank you so much for your reply and for sharing more context, I’m glad to hear the approach fits what you’re looking to achieve.

If you need any further help, please feel free to let me know.

Best regards,

1 additional answer

Your answer

Soul 20 Reputation points

2026-06-13T19:38:06.53+00:00

Thank you so much. I do think this fits exactly what I need.

The 2nd part I didn’t mention would be to separate all non matches to a separate sheet for simplified manual checking/deletion.

I am working within two systems communicating with each other and system 1 is forcing unwanted items into systems 2 excel sheet making it hard to track and edit within the system itself. Like trying to sift through 300 rows with 10 columns 😅.
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-06-13T23:17:37.7966667+00:00

Hi,

You may use the FILTER() function to accomplish this. For specific help, share some data to work with and show the expected results. Share data in a format that can be pasted in an MS Excel file.
Anonymous

2026-06-15T09:49:14.6233333+00:00

Hi Soul,

Thank you so much for your reply and for sharing more context, I’m glad to hear the approach fits what you’re looking to achieve.

If you need any further help, please feel free to let me know.

Best regards,

Answer 1

Use a lookup formula on Sheet2 that pulls values from Sheet1 based on matching values in column A, then copy the formulas down and (optionally) convert to values.

Assume:

Sheet1 has data in A:C
Sheet2 has the keys to match in column A, and B:C should be overwritten from Sheet1.

On Sheet2:

In B2 (to bring Sheet1 column B):

   =INDEX(Sheet1!$B$2:$B$1000, MATCH(A2, Sheet1!$A$2:$A$1000, 0))

In C2 (to bring Sheet1 column C):

   =INDEX(Sheet1!$C$2:$C$1000, MATCH(A2, Sheet1!$A$2:$A$1000, 0))

Fill both formulas down as far as needed.

Only rows where Sheet2!A matches a value in Sheet1!A will return data; unmatched rows will return #N/A. To overwrite with fixed values instead of formulas, select the filled range in B:C, copy, then use Paste Special → Values.

The same approach works across workbooks by fully qualifying the ranges (for example, =[Book1.xlsx]Sheet1!$A$2:$A$1000).

References:

Use Excel built-in functions to find data in a table or a range of cells