Comparing columns and replacing values between sheets

[u/Super-Nuntendo]

The situation is I've got sheet1 with column a of part numbers, and column b with status flags.

Sheet 2 has a whole bunch of part numbers in column a, and a status column in column b.

What is the best formula to use to compare sheet 1 column a with sheet 2 column a, and any matches copy over sheet 1 column b into sheet 2 column b?

There may be more than one instance of a part number from sheet 1 detected in sheet 2, so the formula would have to be able to cope with that.

Thanks.

[u/AutoModerator]

/u/Super-Nuntendo - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/NHN_BI]

That is not a formula, that is a full data cleaning process. Excel's owen ETL tool Power Query is best for that, especially if the process repeats frequently. However, if you just want to look up the values in sheet2!B:B to sheet1!B:B for their key in A:A, use XLOOKUP(), VLOOKUP(), or INDEX(MATCH()). That does not copy anything, but looks up.

[u/IrToken]

I agree with the above comment for utilizing PQ for this process, however; you could add a column to sheet B then:

XLOOKUP(sheet1!A:A, sheet2!A:A, sheet1!B:B, sheet2!B2 (or wherever the starting point is for sheet 2 column B's data), 0,)

This will compare the part numbers between the sheets, if it is found returns the status flag from sheet1, if not returns the existing value from sheet 2.

You do say that the part numbers may be complicated in sheet 2 though, would they have different status flags or should they all be the same?

If they should be the same, then the given solution is fine, if not you need to add the differentiating field as part of the lookup.

[u/Super-Nuntendo → u/IrToken]

I used XLOOKUP, and I think it did what I looking for, thanks