Excel 2007 : Getting The Array Element of a Formula from a Different Cell

**boohah** · 05-09-2009, 02:39 AM

Hi

I have a simple formula searching for the smallest number in 5 cell:

=SMALL(A1:A5,1)

What I would like to do is have this formula reference to another 2 cells which will provide the start row and end row number of the array (i.e. the 1 and the 5). That way i could change it to A7:A1000 or A3:A75 for example. If I make B1 and B2 the start row and end row cells, how could i rewrite the above formula to do this.

Doug

**DonkeyOte** · 05-09-2009, 03:13 AM

Use INDEX

=SMALL(INDEX(A:A,B1):INDEX(A:A,B2),1)

(the above would be preferable to INDIRECT and or OFFSET which may also be suggested given both are Volatile)

EDIT: I should add that the above approach INDEX:INDEX gives the impression of Volatility but is not in fact Volatile.

**boohah** · 05-09-2009, 03:51 AM

Hi Donkey

I actually tried exactly that method before i posted my question as you had shown me the same technique weeks ago. The reason why I didnt run with that idea is that I have a second column with corresponding values and I need the result of that formula in order to calculate the corresponding value in a second column. Lets say that I use your formula in cell B3:

=SMALL(INDEX(A:A,B1):INDEX(A:A,B2),1)

Lets also say that I wanted to find the corresponding value in column C. I would then use the following formula:

=INDEX(C:C,MATCH(B3,INDEX(A:A,B1):INDEX(A:A,B2),0)+1)

Unfortunately when I do this I get errors:

"This function has multiple argument lists. Please select one of them."

Any Ideas?

Doug

**DonkeyOte** · 05-09-2009, 03:56 AM

Your last formula works fine for me (XL2007) ... though without seeing your setup you may need to adjust the C range along similar lines ?

**boohah** · 05-09-2009, 04:45 AM

Hi Donkey

You are right mate, it would have worked. The +1 at the end of the last formula is only correct when sampling from row 1 to row 5. It needs to change as the range changes and that is what is screwing it up. As always I appreciate your help when I am ready to tear my hair out. Thanks mate.

Doug