trying to use cells which have been copied and pasted values only.

[jkmclean]

the following formula [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=(INDEX(MATCH(C105,C100:C160,0),MATCH("week 2",C99:AH160,0))) give a #n/a error Should find 146.83 value
This formula [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX(C100:AH160,6,3) gives the correct answer of 146.83
the week 2 column is from another column and pasted special as values.

What have I done wrong.
Pretty new at this.
John McLean[/FONT][/FONT]


[TABLE="width: 518"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week 1[/TD]
[TD]week2[/TD]
[TD]week 3[/TD]
[TD]week 4[/TD]
[TD]week 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Betty McLean[/TD]
[TD][/TD]
[TD="align: right"]114.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jim Karasimos[/TD]
[TD][/TD]
[TD="align: right"]189[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jack StevenHaagen[/TD]
[TD][/TD]
[TD="align: right"]115.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]John McLean[/TD]
[TD][/TD]
[TD="align: right"]180.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Verna Mortensen[/TD]
[TD][/TD]
[TD="align: right"]146.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ken Killen[/TD]
[TD][/TD]
[TD="align: right"]184.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joyce Banks-Stevenhaagen[/TD]
[TD][/TD]
[TD="align: right"]140.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Alex McKinnon[/TD]
[TD][/TD]
[TD="align: right"]96.333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Linda Carter[/TD]
[TD][/TD]
[TD="align: right"]104.22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Patricia Renaud[/TD]
[TD][/TD]
[TD="align: right"]132.33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Dan Murphy[/TD]
[TD][/TD]
[TD="align: right"]102.43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Evelyn Farrah[/TD]
[TD][/TD]
[TD="align: right"]99.889[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gerry Revelle[/TD]
[TD][/TD]
[TD="align: right"]149.33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Norman Faulkner[/TD]
[TD][/TD]
[TD="align: right"]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Kerry Hietala[/TD]
[TD][/TD]
[TD="align: right"]134[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Don Busch[/TD]
[TD][/TD]
[TD="align: right"]83.167[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[arthurbr]

=INDEX(MATCH(C105,C100:C160,0),MATCH("week 2",C99:AH160,0))) misses the range in which you want to look for (normally =INDEX(C1000:AH160,MATCH...,MATCH...)), and in this formula there is a space between "week" and "2". There seems to be no space in the column header in your sample table

 

[jkmclean]

HI there,
I think I have got it.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX(C100:AH160,MATCH(C100,C100:C160,0),MATCH(E99,C99:AH99,0))
NOw another question.
When I have entered the data for week3 is there a way to copy the column from d13:d57 to f100:f141 automatically?
Again thanks for the help
John[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]_{^{<strike>
</strike>}}[/FONT]