INDEX area selection and reference identity

HotXLS documentation · Calculation and conversion fidelity

Version 2.384.101 extends INDEX(source,row_num,column_num,area_num) to accept the fourth argument for calculated arrays and to compose supported real multi-area references through defined names and LET; row, column and area selectors can broadcast together

One calculated array or multiple reference areas

A constant or calculated array is one source area, so INDEX(SEQUENCE(2,2),2,2,1) returns 4; omitting the fourth argument entirely also selects area 1, while an explicitly empty fourth argument is an invalid area selector

A reference union can contain multiple actual worksheet areas on the same sheet; area_num selects the area in union order, and row and column numbers apply within that selected area

For the examples below, Source!A1:B2 contains {1,2;3,4} and Source!D1:E2 contains {10,20;30,40}; Other!A1:B2 contains {100,200;300,400}

Defined names, LET and CHOOSE

A supported local defined name whose expression is (Source!$A$1:$B$2,Source!$D$1:$E$2) retains both areas; nested names retain that identity, and AREAS(NamedAreas) returns 2

INDEX(NamedAreas,2,2,2) returns 40, while ISREF(INDEX(NamedAreas,0,0,2)) returns TRUE; ROW, COLUMN, ROWS, COLUMNS and aggregate consumers continue to use the selected reference coordinates

Relative reference names resolve at each consumer position, and a worksheet-local name takes precedence over a workbook-scoped name of the same spelling; preserving reference identity does not freeze a relative name to its first evaluation position

LET(areas,(Source!A1:B2,Source!D1:E2),INDEX(areas,2,2,2))
LET(areas,(Source!A1:B2,Source!D1:E2),ISREF(INDEX(areas,0,0,2)))

These expressions return 40 and TRUE respectively; a binding to SEQUENCE(2,2) remains a calculated value array and does not acquire reference identity

A scalar CHOOSE selecting a real reference retains that selected reference, including a choice on another sheet: INDEX(CHOOSE(2,Source!A1:B2,Other!A1:B2),2,2) returns 400 and the corresponding scalar reference result passes ISREF

CHOOSE with an array selector combines values by selector orientation rather than building a union of reference areas; CHOOSE({1,2},Source!A1:B2,Source!D1:E2) returns {1,20;3,40}, and an additional INDEX area selector of 2 still returns #REF!

Three selector arrays

Row, column and area selectors accept scalar values or arrays; singleton dimensions broadcast, opposing row and column orientations form a cross-product, and matching orientations pair positions; unmatched positions in unequal shapes contain individual #N/A errors

INDEX((Source!A1:B2,Source!D1:E2),{1;2},2,{1,2})
INDEX((Source!A1:B2,Source!D1:E2),2,2,{2;1})

The first expression returns {2,20;4,40} and the second returns {40;4}; the selected values retain their string, Boolean, numeric and typed-error representations

If any selector is an array, the result is a value array rather than a reference; a row or column zero, or an omitted row or column selector, selects the first member of that axis at each result position

This differs from scalar-only extraction: two scalar row and column zeros return the whole selected area, but INDEX((Source!A1:B2,Source!D1:E2),0,0,{1,2}) returns {1,10}, not two nested two-by-two arrays; its sum is 11

Area coercion and errors

Area selectors truncate fractional numeric values toward zero and accept numeric text and Boolean values; 1.9, "1" and TRUE select area 1

Area zero, FALSE, an explicitly empty fourth argument, negative values and fractions that truncate to zero return #VALUE!; nonnumeric text also returns #VALUE!, while a positive area beyond the available area count returns #REF!

Typed selector errors propagate with their existing error codes; for an array selector, an invalid area produces an error only at its corresponding result position, so INDEX(SEQUENCE(2,2),2,2,{1,2}) returns {4,#REF!}

Existing errors are not replaced by later bounds checks: INDEX(#N/A,1,1,2) returns #N/A, and INDEX(SEQUENCE(2,2),#N/A,1,#DIV/0!) retains the row selector's #N/A

An invalid reference qualified by an existing local worksheet remains #REF!, including Source!#REF! and 'Plan! O''Brien'!#REF!; a defined name containing that local reference error also retains #REF! when passed to INDEX or ISFORMULA

The local-error token can be used with percent or implicit-intersection operators, and whitespace after the sheet separator is accepted; whitespace between the worksheet name and its separator is outside the accepted syntax

Quoted formula strings such as "Source!#REF!" remain text; a worksheet-qualified reference token inside an array constant is outside the supported source grammar

Row and column selectors keep their existing zero-extraction rules and negative-value validation; area zero never means whole-source extraction

Storage, dependencies and limits

XLSX automatic spill calculation can grow or shrink selector-array results and releases obsolete followers owned by the anchor; supported expressions also evaluate within an explicit fixed CSE destination and survive XLSX save and reopen

A named selector can require conservative dynamic storage even when its evaluated result is scalar; a one-cell dynamic marker does not change the value or a supported reference result's identity

Selected source references and nested names participate in dependency tracking, so subsequent source edits are observed after recalculation; selector traversal and generated matrix payloads consume the existing evaluation-step and array-memory budgets

Boundaries

A direct union whose areas belong to different sheets returns #VALUE!, even when the requested area number would select only one of them; scalar CHOOSE selecting one reference remains the supported cross-sheet alternative

A union of calculated arrays, constants or value expressions is not a real multi-area reference; LET-bound calculated-array unions return #VALUE!, and direct array-of-arrays union syntax is not accepted as a source form

This contract covers the tested local reference and calculated-array forms; it does not imply support for every external-reference graph, every reference-producing function or arbitrary nested array containers

An unknown worksheet qualifier is not normalized as a known local reference error; Excel may create an external-link graph for a formula such as NoSuchSheet!#REF!, and matching that graph is outside this local calculation contract