excel - Convert an Array Formula's Text Results into a Usable Format

Question

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, is there a method of manipulating an Array of text results which I have overlooked? See the bottom of this question for the final desired formula which doesn't work, and request for solutions.

*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)?

Examples where Array Formulas work, where the Result Array is number values

(1) Example 1: 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):

=MAX(IF(VALUE(RIGHT(A1:A500,3))>400,B1:B500,""))

(2) Example 2 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:

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

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.

(3) Example 3 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]: https://stackoverflow.com/a/31325935/5090027

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.

=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))

Example where Array Formulas will not work, where the result array is text values

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.

=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($B$1:$B$500),"")))

So the question is: 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).

Answer 1

As stated previously, there is no native function which can do what you want in a single cell. If you absolutely cannot use VBA, then you could use a helper column (can hide the column if preferred) and then have the cell where you want the result simply show the last cell of the helper column.

Example:

Produce Name   Type
Apple          Fruit
Broccoli       Vegetable
Carrot         Vegetable
Orange         Fruit

Say you want a single cell to show all Fruit results. You could use another column to host this formula. You will be hiding the column later, so let's use one out of the way, like column Z. We also want to easily be able to change what you're looking for, so we'll put the condition in cell D2. In cell Z2 and copied down, you would use this formula:

=IF(B2=$D$2,IF(Z1="",A2,Z1&", "&A2),IF(Z1="","",Z1))

That would result in the following:

Produce Name   Type              Search For   (other columns until you get to Z)      
Apple          Fruit             Fruit                                             Apple
Broccoli       Vegetable                                                           Apple
Carrot         Vegetable                                                           Apple
Orange         Fruit                                                               Apple, Orange

Then in wherever you want your result cell, we'll say D3, simply use this formula to get the last result from your helper column, and then hide the helper column.

=Z5

Which results in the following:

Produce Name   Type              Search For
Apple          Fruit             Fruit
Broccoli       Vegetable         Apple, Orange
Carrot         Vegetable
Orange         Fruit

You could use a dynamic named range instead of simply =Z5 to make sure you're always getting the last cell in your helper column so that your data can grow or shrink and you'll still get the correct result. But now you can change the contents of cell D2 from Fruit to be Vegetable and now the result cell will show Broccoli, Carrot. Hopefully something like this can be adapted to your needs.