simple formula I'm sure using offset? or index?

[Steve]

In cell b1 i have a named range "month". I want to be able to change the
value of month and have the cooresponding values appear in b2-b6. What I'm
after here is the formula I put in b2-b6. Thanks.

a b c d e f
1 Product jan jan feb mar apr
2 prod-a 100 100 200 300 400
3 prod-b 125 125 225 350 450
4 prod-c 175 175 210 325 400
5 prod-d 190 190 220 350 325
6 prod-e 150 150 235 375 390

 

[Pete_UK]

Put this in B2:

=INDEX(C2:F2,MATCH(B$1,C$1:F$1,0))

then copy down.

Hope this helps.

Pete

 

[Don Guillett]

=OFFSET(B2,0,MATCH($B$1,$C$1:$X$1,0))