arraysexcelif-statementexcel-formuladrop

Excel - IF formula TRUE statement output changing based on FALSE argument? (with DROP and VSTACK output)

I have a variable height VSTACK output which can or not have a leading blank value:

<blank>
1
<blank>
2
3
<blank>

I'm trying to remove the leading blank like this:

IF(INDEX(<vstack_output>;1)="";
    DROP(<vstack_output>;1);
    <vstack_output>)

Which evaluates to TRUE and removes the first cell shifting the other values up, but for some reason maintains the last cell in the output with an #N/A error:

1
<blank>
2
3
<blank>
#N/A     // This should not be here

The odd thing is that I've noticed that changing the value of the IF's FALSE statement to a random string...

   IF(INDEX(<vstack_output>;1)="";
        DROP(<vstack_output>;1);
        "random string instead of vstack output")

...actually makes the TRUE statement work as intended, giving the following desired output (note how it not only removes the initial blank, also trims the column, making it once cell shorter):

1
<blank>
2
3
<blank>

The above is also the same output as if I just do DROP(<vstack_output>;1) with no IF involved.

I'm assuming it might have something to do with the way arrays are stored in memory, but otherwise I'm clueless. Any idea of why the TRUE if output might be changing based on the FALSE argument?

Solution

  • You can use implicit intersection to evaluate the top-left cell of the array:

    =DROP(<vstack_output>,@<vstack_output>="")

    And as suggested by @P.b, chuck a LET() variable in the equation just to avoid a 2nd calculation of your vstack-variable.

    =LET(_r,<vstack_output>,DROP(_r,@_r="")