Formula to copy and data found in every fourth column on another sheet

[headley4ever]

Hello Experts,
On the attached file. I need help to write a formula on the sheet name "Score" that will look for the meat name and the corresponding month on the sheet name "Data" and return the number that is in the TKT Avg column. Note that the TKT Avg column is found at every 4th column Please see example i have done for the month Jan-10. The formula i use cannot copy across to the other months. So the formula i need should be able to copy across to the other months

Thanks,
Teddy

[tim201110]

=LOOKUP(B$4,Data!$B$2:$DT$2,Data!$D4:$DV4)

[headley4ever]

Thanks for replying however when i copy the formula across to the other months, Feb-10 & Mar-10 have the same numbers. Can you have a look back at the formula to see if something was omitted.

[headley4ever]

Tim, I tried your formula but when i copy it across to the other months, it return the same numbers for Feb-10 & Mar-10. Please take a look back at the formula to see if you had omitted something.

Teddy

[GeoffW283]

headley: The problem is that your dates in row data!2:2 LOOK the same as the dates in score!4:4 but in fact are not. That's why the LOOKUP is failing.

[Glenn Kennedy]

In B6, copied across and down:

=INDEX(Data!$D4:$CR4,,1+4*(COLUMNS($A:A)-1))

[headley4ever]

Thanks for your observation. Do you have a formula that i could use to obtain the result i want ?

[GeoffW283]

Glen's formula works because it bypasses the date inconsistencies. The other option would be to keep your current LOOKUP formula and make the DATA and SCORE worksheet dates identical.

[headley4ever]

Geof,

Thanks for your explanation. I will keep the lookup formula but ensure that my dates on both sheets are identical.

Thanks again.