Comparing two rows to two rounds and finding what is needed

[acme1]

Hello guys,I hope you can help me and I'll try to explain as much clearly as I can.

So lets say we have two sheets. In both sheets we have like 1700+ rows. Lets assume the sheet2 is correct and we need to compare to sheet 1. In sheet1 and sheet2 we have a product in one column and its code in different.
Lets say:
Product Code
VBP XMZ 1
VBP XMZ 2
KBP 1 A5

So we need to find a code in sheet1 that is united with the wrong product. Like

VBP 1 A5

But for example if there is a code that belongs to some product in sheet2 but it doesnt exist in sheet1 then it is correct. Only need to find that belongs not to the correct product

I hope you'll get it

[Terka Ram J]

Hi acme,

Assuming you want to find the right product in sheet 1(getting the information from sheet 2, paste the below formula in sheet 1 C2

IF(INDEX(Sheet1!$A$2:$A$2000,MATCH(Sheet2!B2,Sheet1!$B$2:$B$2000,0))=INDEX($A$2:$A$2000,MATCH(B2,$B$2:$B$2000,0)),"Correct Product",CONCATENATE("Right Product Code is",INDEX(Sheet1!$A$2:$A$2000,MATCH(Sheet2!B2,Sheet1!$B$2:$B$2000,0))))

The above formula will tell you the product code in sheet 1 should be in which product of sheet 2 (since sheet 2 data is correct data.

if this solves your problem please click "Add Reputation" and mark it as "Solved"
Else revert back

[acme1]

=IF(INDEX(Sheet3!$A$2:$A$2000;MATCH(Sheet2!J389;Sheet3!$I$2:$I$2000;0))=INDEX($A$2:$A$2000;MATCH(J389;$J$2:$J$2000;0));"Correct Product";CONCATENATE("Right Product Code is";INDEX(Sheet3!$A$2:$A$2000;MATCH(Sheet2!J389;Sheet3!$I$2:$I$2000;0))))

I was comparing sheet2 to sheet3. In sheet2 code is in J column and in Sheet3 code is in I column. At first it was perfect but later where code was wrong it says it is correct and where code is correct it says it is wrong. Like we have BBB and formula says right product code is still BBB

[Terka Ram J]

can you provide me with sample data......

[acme1]

Hi again, your formule worked very well. But now I need to add one more formula. I need that in the other column excel automatically pastes all the products and where is correct it leaves the same and where right product code is to change to that product.