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.

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.



"hmm" wrote:

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.

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


"JMB" wrote in message
...
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.



"hmm" wrote:

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.

Thanks guys.

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

"hmm" wrote:

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.