title: Convert an Array Formula's Text Results into a Usable Format
body: <p>When the results of an Array Formula are numbers, I find it generally easy to find an appropriate method to collapse the array into a single result. However when the results of an Array Formula are text, I find it difficult to manipulate the formula in a way which provides a single desired result. In short, <strong>is there a method of manipulating an Array of text results which I have overlooked?</strong> See the bottom of this question for the final desired formula which doesn't work, and request for solutions.</p>
  
  <p><strong>*Edit - after reading through this again, I can alternately summarize my question as: is there a way to access multiple text elements from a 'Formula Array result', without individually selecting (eg: with INDEX)?</strong></p>
  
  <p><strong>Examples where Array Formulas work, where the Result Array is number values</strong></p>
  
  <p><strong>(1) Example 1:</strong> Assume column A rows 1-500 is a list of product ID's in the format of xyz123, and column B rows 1-500 shows total sales for that product. If I want to find the sales for the product with the highest sales, where the last 3 digits of an ID are above 400, I could use an Array Formula like so (confirmed with CTRL + SHIFT + ENTER instead of just ENTER):</p>
  
  <pre><code>=MAX(IF(VALUE(RIGHT(A1:A500,3))&gt;400,B1:B500,""))
  </code></pre>
  
  <p><strong>(2) Example 2</strong> Now assume that column B contains product names, instead of Sales. I now want to simply return the first name which matches criteria of the last 3 digits of the product ID being > 400. This could be done as follows:</p>
  
  <pre><code>=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))&gt;400,ROW(A1:A500),"")))
  </code></pre>
  
  <p>Here, I have done a little manipulation, so that the actual Array part of the formula [IF(RIGHT(A1:A500,3...] returns a value result [the ROWs of the cellsA1:A500 where the last 3 digits are above 400]; I can therefore use MIN to show only the first ROW # which matches, and then I can use that collapsed result in a regular INDEX function.</p>
  
  <p><strong>(3) Example 3</strong> For a final example, see the discussion on a similar question here [Goes more in-depth than my summarized example below, in a way not directly relevant to this question]: <a href="https://stackoverflow.com/a/31325935/5090027">https://stackoverflow.com/a/31325935/5090027</a> </p>
  
  <p>Assume now that you want a list of all product names, where the last 3 digits of the product ID >400. To my knowledge, this cannot really be done in a single Cell, it would have to be done by placing each individual result on a subsequent cell. The following formula could be placed, for example, in C1 and dragged down 10 rows, and would then show the first 10 product names with the product ID's having last 3 digits > 400.</p>
  
  <pre><code>=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))&gt;400,ROW($A$1:$A$500),""),ROW()))
  </code></pre>
  
  <p><strong>Example where Array Formulas will not work, where the result array is text values</strong></p>
  
  <p>Now assume that I want to take the results in Example 3, and perform some text manipulation on them. For example, assume I want to concatenate them all into a single string of text. The below doesn't work, because concatenate won't take an array of results like this as acceptable arguments. </p>
  
  <pre><code>=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))&gt;400,ROW($B$1:$B$500),"")))
  </code></pre>
  
  <p><strong>So the question is</strong>: does anyone know how to get this last formula to work? Or, how to get a formula to work which takes an array of text results, and either converts it into a 'usable range' [so it can be plugged into Concatenate above], or can be manipulated with text arguments immediately [such as mid, search, substitute, etc.]? Right now the only method I can see would be using example 3 above, and then going further and saying, for example, Concatenate(C1,C2,C3...C10).</p>