Format results from using INDEX
 

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.

Format results from using INDEX
 

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.

Format results from using INDEX
 

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.

Format results from using INDEX
 

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.