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) so that the fourth argument is accepted for calculated arrays and supported real multi-area references can be composed 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 counts as a single source area, so INDEX(SEQUENCE(2,2),2,2,1) returns 4; leaving out the fourth argument altogether likewise selects area 1, whereas an explicitly empty fourth argument is an invalid area selector

A reference union may hold several actual worksheet areas on the same sheet; area_num picks the area in union order, and the row and column numbers then apply within that chosen area

In the examples below, Source!A1:B2 holds {1,2;3,4}, Source!D1:E2 holds {10,20;30,40} and Other!A1:B2 holds {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) keeps both areas; nested names hold that identity, and AREAS(NamedAreas) returns 2

INDEX(NamedAreas,2,2,2) returns 40, whilst ISREF(INDEX(NamedAreas,0,0,2)) returns TRUE; ROW, COLUMN, ROWS, COLUMNS and aggregate consumers carry on using the selected reference coordinates

Relative reference names resolve afresh at each consumer position, and a worksheet-local name takes precedence over a workbook-scoped name with the same spelling; preserving reference identity does not pin 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) stays a calculated value array and never acquires reference identity

A scalar CHOOSE that picks a real reference keeps that selected reference, a choice on another sheet included: INDEX(CHOOSE(2,Source!A1:B2,Other!A1:B2),2,2) returns 400 and the matching scalar reference result passes ISREF

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

Three selector arrays

Row, column and area selectors take 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 hold 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 {40;4}; the selected values keep their string, Boolean, numeric and typed-error representations

Where 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, picks the first member of that axis at every result position

This is not the same as scalar-only extraction: two scalar row and column zeros give back the whole selected area, yet INDEX((Source!A1:B2,Source!D1:E2),0,0,{1,2}) returns {1,10}, not two nested two-by-two arrays; the sum comes to 11

Area coercion and errors

Area selectors truncate fractional numeric values towards zero and take numeric text and Boolean values; 1.9, "1" and TRUE all select area 1

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

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

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

An invalid reference qualified by an existing local worksheet stays #REF!, Source!#REF! and 'Plan! O''Brien'!#REF! included; a defined name holding that local reference error likewise keeps #REF! when passed to INDEX or ISFORMULA

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

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

Row and column selectors retain their existing zero-extraction rules and negative-value validation; area zero never stands for whole-source extraction

Storage, dependencies and limits

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

A named selector can call for conservative dynamic storage even where its evaluated result is scalar; a one-cell dynamic marker changes neither the value nor a supported reference result's identity

Selected source references and nested names take part in dependency tracking, so later source edits are picked up after recalculation; selector traversal and generated matrix payloads consume the existing evaluation-step and array-memory budgets

Boundaries

A direct union whose areas sit on different sheets returns #VALUE!, even where the requested area number would pick just 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 genuine 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 implies no support for every external-reference graph, every reference-producing function or arbitrary nested array containers

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