Formula help - need some guidance

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
180
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")))
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
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"))
 
Last edited:

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
180
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

Well-known Member
Joined
Sep 15, 2011
Messages
994
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"))
 

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
180
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!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top