lookup reference from change in value

[JONESY]

Hi Everybody

what I'd like to do is:
In col A there are specific product codes.
In Row 1col B-AZetc there are week no's
Against each specific product code there are prices which remain the same however sometimes these are reduced in a particular week
i want to be able to lookup a particular product code reference against the spreadsheet and for a formula to tell which week it was first marked down.

i've tried playing around with hlookup, match and index but i'm not having any bright idea's

Thanks for the help

L

 

[fairwinds]

Hi,

With weeks in row 1 and prices in row 2, try:

=INDEX(C1:AZ1,MATCH(1,--(C2:AZ2<>B2:AY2),0))

confirmed with Ctrl + shift + enter.

 

[JONESY]

thanks for the help works a treat

If you wouldn't mind could you tell me what the -- does in the array formula?
Really must get to grips with these. Every time I think I'm making inroads into excel something comes along and drags me back!

Thanks Again

L

 

[fairwinds]

(C2:AZ2<>B2:AY2)

Is a condition that returns an array like:

TRUE,TRUE,TRUE,TRUE,FALSE,TRUE

when subjecting it with a mathematical operation like -- ( 2 minus) it converts into numbers.

1,1,1,1,0,1

 

[JONESY]

Thanks for taking the time to explain that!

L