Using Vstack in situ no array formula

Question

Hi

bit of an obscure one, is it possible to use vstack on a non array formula in situ,

So it works for the array spilled by unique but for the formula I can only do it by moving so ;
vstack (a1:a7,"stack" )

Is there a workaround to do this in place , not of any great importance, just curious.

the formula is ;

NDEX( stackT[Item], MATCH( 0, COUNTIF($H$4:H4,stackT[Item]), 0 ) )

Richard.

Answer 1

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

Unfortunately, VSTACK can only be used with array formulas, which means that it won't work with non-array formulas like the one you have.

As for your second question, enclosing a non-array formula with curly braces does not turn it into a spilled array. The curly braces only indicate that the formula was entered as an array formula, but it will still behave like a regular non-array formula.

In summary, if you want to use VSTACK, you will need to convert your non-array formula into an array formula first.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.

Best Regards, Sneha

Answer 2

Thanks anyway.

I don't actually think it's possible.

Richard.

Answer 3

I'm afraid to say I don't have any further ideas to offer.  There are many knowledgeable users active on the forum and I hope that someone else can offer further insight on your issue.

Answer 4

No, I want to use vstak in situ, as I sad above, but I can't as it's not working on a array,

is there a way around this?

Just using ctrl shift enter so;

= INDEX($E$4:$E$19, MATCH( 0, COUNTIF($F$3:F3,$E$4:$E$19 ), 0 ))

has {} around it does not make it a spilled array it is only in each cell?

so still needs to be sent down, ,
is there a way to use vstan on this? I don't think there is

Answer 5

Hello, I am Leonielhou, an Independent Advisor and a user like you, I am happy to help clarify any questions you may have.

VSTACK returns the array formed by appending each of the array arguments in a row-wise fashion. It works equally well for ranges on a worksheet or in-memory arrays created by a formula. It seems that you need to move the formula to another cell or convert it to an array formula first. I find that Ctrl-Shift-Enter is a keyboard shortcut that can be used to enter an array formula or copy data/formula to multiple cells. If you want to use VSTACK on a non-array formula, you can try pressing Ctrl-Shift-Enter after typing your formula. This will convert your formula into an array formula and add curly brackets around it.