Please disregard. I found my error. Sheet4! was not formatted correctly.

"JE" wrote:

Brilliant idea but I'm having trouble in execution for one formula. I added
the VALUE in front of the INDEX formula for one situation and it worked
perfect. The other formula returned a #VALUE! error. This formula is to
return the data from the line above.

Below is the information:

Sheet5

A B
Dated Date: 1/15/1999

Ref CPI m
=VALUE(INDEX(Sheet4!$E$1:$E$136,MATCH(1,(MONTH(She et5!C3)=Sheet4!$C$1:$C$139)*(YEAR(Sheet5!C4)=Sheet 4!$D$1:$D$139),0)+1))

**this one returns the VALUE error**

Ref CPI m+1
=VALUE(INDEX(Sheet4!$E$1:$E$136,MATCH(1,(MONTH(She et5!C3)=Sheet4!$C$1:$C$139)*(YEAR(Sheet5!C4)=Sheet 4!$D$1:$D$139),0)))

**this one returns the correct value**

Sheet4

A B C

3 1997 159.1
4 1997 159.6
5 1997 160
6 1997 160.2
7 1997 160.1
8 1997 160.3
9 1997 160.5
10 1997 160.8
11 1997 161.2
12 1997 161.6
1 1998 161.5
2 1998 161.3
3 1998 161.6
4 1998 161.9
5 1998 162.2
6 1998 162.5
7 1998 162.8
8 1998 163
9 1998 163.2
10 1998 163.4
11 1998 163.6
12 1998 164


"Jim Thomlinson" wrote:

Sounds like your index match is returning text. Try using the Value function
to coerce the returned value to a number (instead of multiplying by 1)...
=value(index(...(match(...)))
--
HTH...

Jim Thomlinson


"JE" wrote:

This may be an obtuse question. I used the INDEX/MATCH function to return a
number from another worksheet. I am using this number in another forumula
and receive #VALUE!. However, when I type over the number returned from the
INDEX/MATCH, the formula is successful. I attempted to multiply 1 times the
end of the INDEX formula but that did not work. Any suggestions?

Many thanks.