using INDIRECT function with dynamic array formula syntax (Excel O365)

Question

I am using the new dynamic array functions introduced in Excel in 2018 (e.g. SEQUENCE, UNIQUE etc. functions). I have a list of cell references that are generated dynamically and would like to apply the INDIRECT function to these list items. A simplified example:
cell A1: =SEQUENCE(5) (results in rows column A values 1,2,3,4,5 as expected)
cell B1: ="A"&A1# (results in rows column B values A1, A2, A3, A4, A5 as expected)
cell C1: =INDIRECT(B1#) this should give me rows in column C values 1,2,3,4,5, but in fact gives me #VALUE ,#VALUE ,#VALUE ,#VALUE ,#VALUE So the formula properly recognizes the number of rows of the original dynamic array, but for some reason does not dereference the cells properly. The strings seem to be of the proper format - a simple string function such as LEN also works: setting C1 to =LEN(B1#) results in 5 rows of the value 2.

The syntax per se seems to be OK.. for the special case of =SEQUENCE(1) in cell A1 everything works as intended. I tried the R1C1 reference format also, same result

EDIT Overall I am trying to achieve the following

• import a list form a non-Excel data source - list is not a dynamic array, it's just a TSV import. I don't know beforehand how many items are in this list, and it can vary a lot
• do several different calculations on values of this list.
• so far my approach was to use the COUNT function to determine the number of items in the imported list, and then use that to create the second list using SEQUENCE and INDEX to retrieve values.
• the problem arises for some calculations where the data contains references to other rows so I have to use indirect addressing to get at that data

Hawse

You are correct, I am using Office 365. I misread Microsoft's announcement that this would come to Excel 2019. Sorry for the confusion. Edited title accordingly – Hawse

Answer 1

I found a very interesting point about this function. Although we all assume that Indirect does not work with array, but in this case it seems to work. For example, I have 3 cells with values: A1=3,B2=4,C3=5. If I write =INDIRECT({A1;B2;C3}) it returns #VALUE!. But if I write =CELL("contents",INDIRECT({A1;B2;C3})), it returns an array of 3,4 and 5. The same goes to your case with CELL and INDIRECT. And if I wrap it with a function like SUM or COUNT, it still works. The CELL + INDIRECT formula still works in the older Excel versions. However, the combination of SUM, CELL, and INDIRECT doesn't. So if you just want to achieve what you want, this is already the solution. If you want to achieve more, I suggest changing to another answer.

Brio

@ciso Actually after spending time researching I have an assumption about it and I published an article in microsoft forum: link – Brio

Thanks for this! INDIRECT does indeed accept arrays, but returns some kind of array of objects that Excel cannot directly use. (It will correctly evaluate them with F9 though.) Minh's article indicates that one can use N(INDIRECT([range or other array valued function])) which is quite concise. – Ayah

Answer 2

The INDIRECT function cannot accept an array for an argument.

In other words:

=INDIRECT({"a1","a2"}) --> #VALUE! | #VALUE!

So you could, for example, refer to each cell in column B as a single cell:

eg:

C1: =INDIRECT(B1)

and fill down.

Depending on how you are using this, you could also use the INDEX function to return an individual element

To return the third element in the array generated by B1#:

  =INDIRECT(INDEX(B1#,3))

EDIT:

After reading your comment, and depending on details you have not shared, you may be able to use a variation of the INDEX function.

For example, to return the contents of A1:A5, based on your SEQUENCE function, you can use:

=INDEX($A:$A, SEQUENCE(5))

but exactly how to apply this to your actual situation depends on the details.

Torchier

it's the manual "fill down" I'm trying to avoid because my data has a variable number or rows. – Hawse

@Hawse This seems like an XY problem. Probably, you need to ask a different question. Or add considerable detail to this one. – Torchier

@Hawse See my Edit – Torchier

Answer 3

As Rosenfeld points out, INDIRECT() does not accept an array as an input. If you need a function that:

1. "acts" like INDIRECT()
2. can accept an array as an input
3. can return an array as an output

Then we can make our own:

Public Function Indirect_a(rng As Range)
    Dim arr, i As Long, j As Long
    Dim rngc As Long, rngr As Long
    
    rngc = rng.Columns.Count
    rngr = rng.Rows.Count
    ReDim arr(1 To rngr, 1 To rngc)
    
    For i = 1 To rngc
        For j = 1 To rngr
            arr(j, i) = Range(rng(j, i).Value)
        Next j
    Next i
        
    Indirect_a = arr
End Function

and use it like:

Since it creates a "column-compatible" array, it will spill-down dynamically in Excel 365.
It can be used in versions of Excel prior to 365, but it must be array-entered into the block it occupies.

Ing

Answer 4

You can use the following formula =BYROW(B1#,LAMBDA(a,INDIRECT(a)))

Salsify

Answer 5

I found a way using INDEX and MAKEARRAY:

=MAKEARRAY(rows;columns;LAMBDA(r;c;INDIRECT("A"&INDEX(B4#;r;c))))

INPUT example: rows = 3; columns = 1; A1# = {c;d;e}; B4# = {1;2;3}

OUTPUT: {c;d;e}

Shadowy