Help with an array

[Robert A]

={SUM(VLOOKUP($B36,Returns,COLUMN(INDIRECT($B$5:$B$25)))*D$5$25)}

This formula won't work as an array, but it will work like this:

=(VLOOKUP($B36,Returns,COLUMN(INDIRECT($B$5))*D$5

Either way, the Vlookup portion works okay, as does INDIRECT, but the COLUMN stops working when I convert it to an array.

Help is sincerely appreciated!
___________________________
Robert Ades Accountancy Corp.
12100 Wilshire Blvd., Suite 660
Los Angeles, CA 90025
Tel: 310-571-1224
Fax: 310-571-1227

(e-mail address removed)

 

[Jerry W. Lewis]

You do not type the curly brackets for an array formula. Simply type
=SUM(VLOOKUP($B36,Returns,COLUMN(INDIRECT($B$5:$B$25)))*D$5$25)
and press Ctrl-Shift-Enter

If you do it right, the formula bar will surround the entire formula in
curly brackets to show that it was accepted as an array formula
{=SUM(VLOOKUP($B36,Returns,COLUMN(INDIRECT($B$5:$B$25)))*D$5$25)}

Note that this also requires that you have defined the name "Returns"

Jerry

 

[Robert A]

Jerry: I got those bases covered - I entered the array properly, and the
named cell reference is properly defined.

Robert

 

[Peo Sjoblom]

What are you trying to do?

 

[Robert A]

That's a little hard to explain. Could I send my worksheet to you directly?

Robert Ades