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).

There is no native-function solution for array-formula concatenation when the number of bits to be concatenated is not known beforehand. A simple UDF replacement for the stunted native CONCATENATE() will produce the results you are looking for with the obvious drawback of requiring macros. — Excel Hero, Oct 13 '15 at 20:55
@ExcelHero Yes unfortunately I'm looking for a non-macro solution, as I am not always able to use macros depending on the circumstances of the workbook. Thanks for the additional information about why concatenate fails there. — Grade 'Eh' Bacon, Oct 13 '15 at 21:01
I was surprised some years back that CONCATENATE doesn't work on an array. I don't think there are any formulae based workarounds (tried a few using `N` etc, didnt work). — brettdj, Oct 16 '15 at 08:20
Do you agree that for example to list the results of example 3 you will have to use a "working" column, if so then it would acceptable with one formula to obtain the concatenation of the result of example 3, regardless their number. It's that what you are looking for? — EEM, Oct 16 '15 at 17:50
@EEM I can see how to concatenate a 'working column' method along the lines of what I have in example 3 above. What I'm really looking for are alternate methods of accessing the information - most preferably without needing a working column. — Grade 'Eh' Bacon, Oct 16 '15 at 17:53
I guess you meant by using =CONCATENATE(a,b,c,...). What I'm referring to is 1) to generate an array (usable range as you called it) with the concatenated results, 2)to pick from it the item needed, however to perform the second step the array must exist physically therefore the use of the working column. This solution no need to build the concatenation formula, which is always a problem to keep updated. It’s similar to what you have in mind probably, but with fewer steps combining some tools you have not mentioned in you examples. — EEM, Oct 16 '15 at 18:28
So the answer to your question is the array can be generated (or the usable range as you called it), but to pick the full concatenation it has to exist physically. — EEM, Oct 16 '15 at 18:28
When I refer to a 'Usable Range', I mean to take an Array of text values and convert it into a format susceptible to manipulation by, for example, CONCATENATE/SUBSTITUTE/SEARCH/etc. I can see how to do this *after* an Array has been converted to individually pulled values - look at the last sentence of my post; that's how I would approach doing this currently. Now, I'm looking for ideas on alternate means to access elements of an Array of text values, apart from pulling them individually (eg: with INDEX). — Grade 'Eh' Bacon, Oct 16 '15 at 18:31

score 4 · Answer 1 · answered Oct 13 '15 at 23:11

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.

Thanks for this detailed explanation. I can see how to bring results back using line by line helper columns, and I can see how using this method without Array Formulas is cleaner than attempting what I have shown in Example 3. The benefit of your method is that since I already need some helper cells to build up the total list of matches, I might as well do so with a simpler formula inside an entire helper column. Unfortunately this is not quite what I'm after - which is specifically how to access a text element from an array without individually selecting it. — Grade 'Eh' Bacon, Oct 14 '15 at 14:18

bidout · Answer 2 · 2015-10-22T01:52:32.183

To reiterate other responses, I did not find a way to use the concatenate function on an array. However, I did find a way to concatenate the "product names" using only one array function and no so-called "helper column." Although it is rather long and tedious, I think this may add to the discussion. For one, if you are actually going to use a formula like this for some valid purpose or to overcome a specific barrier, it can be easily used via copying and pasting of the formula (that is, it is actually relatively adaptable). On the other hand, if your interest is more a curiosity, my answer may be more banal than you might like.

In my simulation of your problem, I also had two columns, but shortened the row count to 40. The leftmost column ("C") contains sequences of three letters and three numbers, while the right column ("D") contains random sequences of letters and numbers that simulate your "product names."

I used a combination of nested replace and concatenate functions. The function below is chopped to focus on the "base unit" of the agglomerated function.

Base Unit

REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)))=TRUE,””,

The above formula essentially looks at the first product name with a corresponding product ID with numerical sequence > 400, then replaces it with a concatenation, given that there exists another product meeting the same product ID criteria. This can be thought of as a "accumulating" concatenation, starting at the innermost parentheses. This "base unit" of the formula can be repeated to an arbitrary extent. That is, if you believe that there are anywhere from 200 to 280 products in the list meeting the product ID criteria you set, you can repeat this base code 280 times. As you see, if the formula attempts to concatenate product names that do not exist (you have 280 formula base units and only 275 products meeting the criteria), the formula self-terminates...in a sense. It actually begins to concatenate nothing over and over again until all base units are enacted. The result will be all desired product names concatenated in one cell, with a period separating each one.

Only one number changes from base-block to base-block, and that is the kth element of the SMALL array. These variables will obviously step by one in each base unit. For my test, I used 14 base units.

Complete Formula with 14 Base Units

=REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)))=TRUE,””,**REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),14)))=TRUE,””,INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),14)))))))))))))))))))))))))))))))))))))))))

Obviously, if you look at the entire formula, it is pretty indecipherable. But, looking at it in terms of base units, you may see how it can be easily constructed then copied and pasted (after writing the initial base unit, it took about 2 minutes to put it all together).

Okay this is a little interesting - I like the approach you have overall to avoid errors if the number of results is less than the max number possible. However as you say this is a bit of a tedious thing, and still uses INDEX to pull the results, which means there's a lot of repetition involved. Something to think on, for sure. — Grade 'Eh' Bacon, Oct 22 '15 at 12:13
Note that on reflection I believe the simplest way to use a similar method to yours is the following formula - One of the benefits is that using & instead of Concatenate eliminates the need to have dozens of ending brackets, as each item is its own term. Simply add a new section of the 'base' formula, and iterate the kth value by 1 each time. =INDEX($B$1:$B$500,SMALL(IFERROR(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),""),1))&INDEX($B$1:$B$500,SMALL(IFERROR(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),""),2)) — Grade 'Eh' Bacon, Oct 22 '15 at 14:58
I see your point. Definitely a simplification to my approach.I think this is an important and well-developed question. Definitely worth thinking deeply about..."why does there seem to be such a limitation in Excel's ability to deal with Formula Array results?" Obviously, as others have noted, a VBA solution is extremely easy. Therefore, I think Microsoft should incorporate. ie fix CONCATENATE(), for goodness sake. — bidout, Oct 22 '15 at 15:00

score 2 · Answer 3 · answered Feb 16 '16 at 19:11

This is a VBA-free solution using Get&Transform in Excel 2016 or the Power Query Add-In for versions before:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractLast3Digits = Table.AddColumn(Source, "Value", each Text.End([ProductID],3)),
    ChangeToNumber = Table.TransformColumnTypes(ExtractLast3Digits,{{"Value", type number}}),
    FilterAbove400 = Table.SelectRows(ChangeToNumber, each [Value] > 400),
    Concatenate = Text.Combine(FilterAbove400[ProductName])
in
    Concatenate

You can perform all sorts of text manipulation on the “array-output” (Step “FilterAbove400”), in this example I’ve just concatenated without separators as I understood your request.

It takes your input data that should be in table-form and named “Table1” in the 1st step (Source).

Link to file with solution: https://www.dropbox.com/s/utsraj0bec5ewqk/SE_ConvertArrayFormulasTextResult.xlsx?dl=0

Interesting; one of a few new Excel 2016 features I like the look of. Unfortunately for my workplace, neither Excel 2016 or a Power Query Add-In would necessarily be available for all users accessing a workbook. — Grade 'Eh' Bacon, Feb 16 '16 at 19:16

Brian Pressler · Answer 4 · 2015-10-22T20:36:27.820

1

You can create your own aggregate function to handle the results of a formula array. It does require a little VBA... but it's not difficult. This will allow you to do all kinds of string manipulation or numerical analysis on arrays of values.

To do your concatenation function, open up a VBA code window and create a new module by right clicking on the project -> insert -> new module. Double click the new module and insert this code to create the function that will concatenate an array into one large string:

Function ConcatenateArray(ParamArray Nums() As Variant) As Variant
Dim BigString As String
Dim N As Long
Dim A() As Variant
Let A = Nums(0)

BigString = ""
For N = LBound(A) To UBound(A)
    BigString = BigString & A(N, 1)
Next
ConcatenateArray = BigString

End Function

Then change your array formula in the cell to:

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

Of course you have to hit CTRL + SHIFT + ENTER instead of just ENTER to confirm the cell as an array formula.

edited Oct 22 '15 at 20:36

answered Oct 22 '15 at 19:14

Brian Pressler

6,653
2
19
40

I appreciate the detailed code for a UDF solution. Unfortunately depending on the circumstances of the workbook, VBA may not be possible (ie: when the file is being distributed to a group with a strict 'no-macro' policy). – Grade 'Eh' Bacon Oct 22 '15 at 19:18
Understood... but hopefully this will help others without that restriction. It's a very powerful extension of Excel. – Brian Pressler Oct 22 '15 at 19:44
I agree - this is a great solution to most instances of the problem. – Grade 'Eh' Bacon Oct 22 '15 at 19:45

EEM · Answer 5 · 2015-10-22T16:43:50.727

I would try to address the several question raised in this post:

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],

Even if the first part of this question is feasible, the last part (i.e. "[so it can be plugged into Concatenate above]" is not possible as the CONCATENATE function does not take ranges as argument.

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).

That’s certainly one method, but please give a try to this:

Let's start from this:

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.

But first let’s assume the following:

-. Data range is located at D10:F510 and includes fields: Product, Product, Sales and Product Name (Selection)*

*used to list results from formula in example 3

.- Data contains 23 records complying with the criteria defined in example 1 (see Fig. 1)

.- Value 400 is enter in cell E4 to ease modifications to the criteria instead of hard code in the formulas (see Fig. 3).

Fig. 1

Now, in order to generate an Array with the concatenated results and to post it a usable range, let’s apply a minor modification to the formula in example 3. Enter this FormulaArray in G11 and copy till last record (not just 10 lines)

=TRIM(CONCATENATE(
IF(ROW(G11)-ROW(G$11)+1=1,"",G10)," ",
IFERROR(INDEX($E$11:$E$510,
SMALL(IF(VALUE(RIGHT($D$11:$D$510,3))>$E$4,ROW($D$11:$D$510)-ROW($D$11)+1,""),
ROW(G11)-ROW(G$11)+1)),"")))

Fig. 2

The in the Summary section located at D4:E8 we have the results from examples 1 & 2 and the Concatenated results with the list of selected products (see Fig. 3). Enter this formula in E8 (suggest to increase the row height to the max of 409 and Wrap Text to true)

=INDEX($M$11:$M$510,1+MAX(ROW($M$11:$M$510))-ROW($D$11))

Fig. 3

As regards this question:

Is there a way to access multiple text elements from a 'Formula Array result', without individually selecting (eg: with INDEX)?

On this particular case (i.e. concatenation of array elements) I would apply a different perspective and generate the array with concatenated results then to pick the needed element, even if the use of INDEX is required.

Last I would like to make a minor note about these formulas:

Example 2:

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

If the data range does not start at Row 1 use this formula instead:

=INDEX($E$11:$E$510,MIN(IF(VALUE(RIGHT($D$11:$D$510,3))>400,
1+ROW($D$11:$D$510)-ROW($D$11),"")))

Example 3:

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

If the data range does not start at Row 1 use this formula instead:

=IFERROR(INDEX($E$11:$E$510,
SMALL(IF(VALUE(RIGHT($D$11:$D$510,3))>$E$4,
1+ROW($D$11:$D$510)-ROW($D$11),""),
1+ROW()-ROW($K$11))),"")

I can see how to do all of this with a helper column - and I understand what you mean by 'concatenate first and then retrieve a single result'. However this still does not provide a solution within a single cell, which may not be possible (unless the same formula is repated many times, as in @bidout's formulas above). Note that for your drag-down formula I would simply have the formula as follows, to eliminate the need for an Array Formula at all (starting at G11 & dragged down [with G10 being blank]): =G10&IF(RIGHT(D11,3)>400,E11,"") which is effectively the answer provided by TigerAvatar. — Grade 'Eh' Bacon, Oct 22 '15 at 17:05

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

5 Answers5

Linked