title: Use index, match, and row/column excel functions in a formula to return a subset array with just cells with text and exclude the empty ones
body: <p>I have this scenario where i want to extract a subset of a range in one row (using index, match and row functions preferably )  and return only smaller filtered array with cells that contain text/data and exclude empty cells .. I want to use this filtered array for further processing</p>
  <p>The input 2-D array i want to get a subset array from is colored</p>
  <p><a href="https://i.stack.imgur.com/v37KD.png" rel="nofollow noreferrer">The image shows the returned array size in last column </a></p>
  <p>A dropdown menu is provided for the user to select the desired row (say A1:A6).</p>
  <p>The row selected by the user determines which row of the 2-D colored range (say B1:G6 ) to extract the filtered array from
  then only cells with text/data are returned from that row</p>
  <p>I tried the following code</p>
  <p>I can get the array of whole correct row (all cells) using</p>
  <pre><code>index(B1:G6, match(A1:A6,value_from_dropdown_menue,0),0)
  </code></pre>
  <p>then i tried to apply row() function to filter out empty elements from that returned array .. lets refer to the <strong>returned_full_row_array</strong> as <strong>RFRA</strong> for readability</p>
  <pre><code>index(RFRA, row(indirect(&quot;1:&quot;&amp; counta(RFRA))))
  </code></pre>
  <p>the full formula looks like this (<strong>B8</strong> is the cell with dropdown )</p>
  <pre><code>=INDEX(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0),ROW(indirect(&quot;1:&quot;&amp; counta(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0)))))
  </code></pre>
  <p>but the row() function is not returning an array and thus i don't any array returned .. i only get the first element.. even if i hardcoded row(1:3) .. i don't get 3 element array back ... only the first element of the the whole row</p>
  <p>any ideas?</p>
  <p>Edit : I can successfully get the whole row of my range using index match
  the result is a fixed size 1 by 6 array like this (example first and 2nd row ):</p>
  <pre><code>for 1st row &gt;&gt; {&quot;M1-item1&quot;,0,0,0,0,0}
  for 2nd row &gt;&gt; {&quot;M2-item1&quot;,&quot;M2-item2&quot;,&quot;M2-item3&quot;,&quot;M2-item4&quot;,0,0}
  </code></pre>
  <p>desired output however should be a <strong>variable length array</strong> without the empty/zero elements :</p>
  <pre><code>for 1st row &gt;&gt; {&quot;M1-item1&quot;}
  for 2nd row &gt;&gt; {&quot;M2-item1&quot;,&quot;M2-item2&quot;,&quot;M2-item3&quot;,&quot;M2-item4&quot;}
  </code></pre>