if(row(),formula) Evaluate construct returns 1st array value only

[Jon von der Heyden]

Hi All

Say I have the following:

Selection.Value = "if(row(),vlookup('Test'!$A$2:$A$10,Test!$D$2:$E$4,2,false))"

Although the match value is an array, I would have assumed the result to return an array.

Selection is same dimension (9 rows, 1 column).

I have also attempted the same using index alternative to if(row()... And I have tried an INDEX MATCH approach, instead of VLOOKUP.

Any idea why it isn't behaving as expected?

Edit: Some debugging info if it helps... The result is definitely an array result i.e. Variant(1 to 9, 1 to 1). Every value in the array is the result of the first vlookup, i.e. vlookup('Test'!A2,'Test'!D2:E4,2,false).

Regards
Jon

 

[RoryA]

I assume there is in fact an Evaluate in the code and an address passed to ROW()?

Assuming so, you need a little extra work to force the passing of the array of entries - for example:

Selection.Value = Evaluate("if(row(A2:A10),vlookup(INDEX('Test'!$A$2:$A$10,N(IF(1,ROW($A$2:$A$10)-min(row($A$2:$A$10))+1))),Test!$D$2:$E$4,2,false))")

 

[Jon von der Heyden]

I assume there is in fact an Evaluate in the code and an address passed to ROW()?

Yes, sorry just me being me, and copying one of my lame attempts from the immediate window.

Assuming so, you need a little extra work to force the passing of the array of entries - for example:

Selection.Value = Evaluate("if(row(A2:A10),vlookup(INDEX('Test'!$A$2:$A$10,N(IF(1,ROW($A$2:$A$10)-min(row($A$2:$A$10))+1))),Test!$D$2:$E$4,2,false))")

Epic! Works a treat! Thanks Rory.

 

[Jon von der Heyden]

Ugh! I went down the formula route after all. This (fantastic) solution increases the character count so much I have instances exceeding 255 chars. I tried creating holding names for the instances where they are needed but then it reverts back to the 1st array item value.

 

[Jon von der Heyden]

Some extra potentially helpful information for anybody reading this topic:

https://fastexcel.wordpress.com/201...to-make-excels-evaluate-method-twice-as-fast/