INDEX formula returns #REF! error for ranges larger than two cells

[hmm]

I am using the INDEX function twice [form:
AVERAGE(INDEX(lowerlimit):INDEX(Upperlimit))] to specify a sub-range of cells
within column, utilizing MATCH to find the row numbers of the lower and upper
limits. It has worked beautifully before, but, mysteriously, today I am
finding that the formula will give a #REF! error for any subrange larger than
two cells; that is, the second index must refer to the cell following the
first index, otherwise I get the error.

Can anyone give me a clue as to what the problem might be, so that the
formula can be fixed to return the expected value?

Thanks.

 

[JMB]

The problem is not with the fact the subrange is larger than two cells.

The only thought I have is to double check your range references to ensure
Match is not returning a value that is outside of the range referenced by
Index. For example

=Index(A1:A10, 15)
will return #REF as there are not 15 cells in the range A1:A10.

If that is not the problem, you should post your exact formula instead of a
general outline.

 

[T. Valko]

Just to add:

utilizing MATCH to find the row numbers

Also note that the result of MATCH is *relative* to the range.


--
Biff
Microsoft Excel MVP

The problem is not with the fact the subrange is larger than two cells.

The only thought I have is to double check your range references to ensure
Match is not returning a value that is outside of the range referenced by
Index. For example

=Index(A1:A10, 15)
will return #REF as there are not 15 cells in the range A1:A10.

If that is not the problem, you should post your exact formula instead of
a
general outline.

I am using the INDEX function twice [form:
AVERAGE(INDEX(lowerlimit):INDEX(Upperlimit))] to specify a sub-range of
cells
within column, utilizing MATCH to find the row numbers of the lower and
upper
limits. It has worked beautifully before, but, mysteriously, today I am
finding that the formula will give a #REF! error for any subrange larger
than
two cells; that is, the second index must refer to the cell following the
first index, otherwise I get the error.

Can anyone give me a clue as to what the problem might be, so that the
formula can be fixed to return the expected value?

Thanks.

 

[hmm]

Thanks guys.

A very quirky problem: today it's working! Next time it happens I should
place the entire formula, as JMB suggested.