Get element from array constant?

user_784192 0
Viewed 101

If you have a CSE array constant as follows in cell A1:

{={2,4,6,8}}

How can you get a specific element from the array constant? I tried the following formulas but they all return the first value of the array constant (2).

=INDEX(A1, 0)
=INDEX(A1, 1)

However, it does work if the array is not a reference. The following formula returns the 3rd element (6).

=INDEX({2,4,6,8},3)

Thank you

excel-formula excel
2 Answers

You could put the array constant in a Name instead of a cell.

array constant as named range

Then INDEX will work with it properly with no implicit intersection.

Or you could parse the formula using the FORMULATEXT function, but that sounds tedious.

dkusleika_42800

Try =INDEX(A1#,1). The # tells excel that A1 is a spill formula (more than 1 cell long). The array index starts at 1, not 0 in this case.

As a side note, Index knows you are referring to an index, not a row, when you give it a 1D array. In your example =INDEX(A1#,4) and =INDEX(A1#,1,4) return the Fourth item in your array (8 in this case), but =INDEX(A1#,4,1) will give you the error #REF!. If you define your array vertically {={2;4;6;8}}, =INDEX(A1#,4) and =INDEX(A1#,4,1) both work.

Edit: It looks like this does not always work in Excel 365 when a an array formula is created using Ctrl+Shift+Enter. I think this is due to the changes in 365. Array formulas have pretty much been replaced with spill formulas. Entering ={2,4,6,8} is mostly equivalent to a pre-365 array formula, but creating an array formula with Ctrl+Shift+Enter confines the output to as many cells as selected. In Dan's case, he selected only one cell and the formula doesn't automatically spill, so the formula is confined only to that cell. Excel seems to treat that cell as if it only contains that array element. If you select 2 cells and enter an array formula then =INDEX(A1#,2) will work but =INDEX(A1#,3) returns #REF!.

Edit2: It is possible with the FORMULATEXT Function as @DickKusleika suggested. Here is a function adapted from ExcelJet that does the job.

=LET(
    DesiredIndex, 1,
    ArrayFormulaRef, A1,

    Formula, FORMULATEXT(ArrayFormulaRef),
    FormulaLen, LEN(Formula),
    CSVStart, FIND("{",Formula,2),

    CSV, MID(LEFT(Formula,FormulaLen-1),CSVStart+1,FormulaLen),

    TRIM(MID(SUBSTITUTE(CSV,",",REPT(" ",LEN(CSV))),(DesiredIndex-1)*LEN(CSV)+1,LEN(CSV)))
)
user_93930940
Related