Formula help - need some guidance

[Camel123]

Hello guys,

Would appreciate some guidance. I have three sheets named "check", "current" and "previous". Column description in all sheets are B= product number, C= purchase price, D= sales price.

"Current", values: B3= 77, C3= 100, D3= 150

"Previous", same values appear on a different row: B10=77, C10= 120, D10= 150.


"Check": in this sheet, indicate whether there have been any changes in the information related to product number 77. In this case indicate "Change" on C3 vs C10 and "Unchanged" on D3 vs D10.

I created a formula that works if the information for the specific product is on the same row number but that might not be the case everywhere so I need a work-around on this:

In the "Check" sheet column C= purchase price
=IF(($B3)="";"";IF(C$2=current!C$2;IF(EXACT(current!C3;previous!C3);"Unchanged";"Changed")))

In the "Check" sheet column D= sales price
=IF(($B3)="";"";IF(D$2=current!D$2;IF(EXACT(current!D3;previous!D3);"Unchanged";"Changed")))

 

[wrightyrx7]

Could compare 2 vlookups maybe?


Purchase price
=IF(B3="","",IF(VLOOKUP(B3,Current!B:C,2,0)=VLOOKUP(B3,Previous!B:C,2,0),"Unchanged","Changed"))

Sales Price
=IF(B3="","",IF(VLOOKUP(B3,Current!B:D,3,0)=VLOOKUP(B3,Previous!B:D,3,0),"Unchanged","Changed"))

 

[Camel123]

Thank you mate it works, though I need to change the column index manually and I have loads of columns. Could you maybe replace vlookup with index/match and then I will try to understand and interpret the formula? I will try do it myself until response

 

[wrightyrx7]

Thank you mate it works, though I need to change the column index manually and I have loads of columns. Could you maybe replace vlookup with index/match and then I will try to understand and interpret the formula? I will try do it myself until response

You mean like this?

=IF(B4="","",IF(INDEX(Current!C:C,MATCH(Check!B4,Current!B:B,0))=INDEX(Previous!C:C,MATCH(Check!B4,Previous!B:B,0)),"Unchanged","Changed"))

 

[Camel123]

Thanks a lot mate, really appreciate it since I am also learning from it. Clear to me on how it functions and what all functions does!