Loading documentation…
Loading documentation…
This page lists the 17 functions of the lookup and reference family. Taxonomy/source: Lookup and reference functions. The formula function contract has every family, the engine builds, and the evaluation limits.
All functions
| Function (aliases) | Engine | Contract |
|---|---|---|
INDEX | Default + full | |
MATCH | Default + full | |
VLOOKUP | Default + full | |
HLOOKUP | Default + full | |
XLOOKUP | Default + full | |
XMATCH | Default + full | |
CHOOSE | Default + full | |
ROW | Default + full | |
ROWS | Default + full | |
COLUMN | Default + full | |
COLUMNS | Default + full | |
ADDRESS | Default + full | |
OFFSET | Default + full | |
INDIRECT | Default + full | |
AREAS | Default + full | |
FORMULATEXT | Default + full | |
HYPERLINK | Default + full |
Argument order, required/default state, accepted shapes, repetition, and return shape come directly from the inventory.
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
row | yes | none | scalar, reference | once |
column | no | 1 | scalar, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
lookupArray | yes | none | range, array, reference | once |
matchMode | no | 1 | scalar, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
table | yes | none | range, array, reference | once |
index | yes | none | scalar, reference | once |
approximate | no | true | scalar, reference | once |
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
lookupArray | yes | none | range, array, reference | once |
returnArray | yes | none | range, array, reference | once |
ifNotFound | no | #N/A | scalar, reference | once |
matchMode | no | 0 | scalar, reference | once |
searchMode | no | 1 | scalar, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
lookupArray | yes | none | range, array, reference | once |
matchMode | no | 0 | scalar, reference | once |
searchMode | no | 1 | scalar, reference | once |
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
index | yes | none | scalar, reference | once |
value | yes | none | scalar, range, array, reference | one-or-more |
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
reference | no | formula-cell | range, array, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, range, array, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
row | yes | none | scalar, reference | once |
column | yes | none | scalar, reference | once |
absNumber | no | 1 | scalar, reference | once |
a1 | no | true | scalar, reference | once |
sheetText | no | none | scalar, reference | once |
Return shape: reference.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
reference | yes | none | range, reference | once |
rows | yes | none | scalar, reference | once |
cols | yes | none | scalar, reference | once |
height | no | reference-height | scalar, reference | once |
width | no | reference-width | scalar, reference | once |
Return shape: reference.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
a1 | no | true | scalar, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
reference | yes | none | range, reference | once |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
linkLocation | yes | none | scalar, reference | once |
friendlyName | no | none | scalar, reference | once |
These values are normative for the listed Sheetwrite subset. function-defined and contextual are explicit limitations: consult the formula guides' function-specific sections rather than assuming another spreadsheet's edge behavior.
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: function-defined result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: insensitive wildcard: function-defined |
| environment | locale: invariant dateSystem: function-defined |
| numeric | tolerance: exact domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
| Dimension | Contract |
|---|---|
| shape | scalar: contextual range: contextual array: rejected broadcast: none result: reference |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: insensitive wildcard: literal |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: exact domain: bounded iteration: kind: none maximum: none |
| calculation | dependencies: dynamic lazy: function-defined spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: preserved text: preserved boolean: preserved error: function-defined |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: exact domain: all-inputs iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: rejected array: rejected broadcast: none result: scalar |
| coercion | blank: preserved text: preserved boolean: preserved error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: exact domain: all-inputs iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
| Dialect | Status |
|---|---|
| Microsoft Excel | divergent |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: OFFSET and INDIRECT are volatile and return references. Multi-cell results spill when used as formula results; reference-taking functions consume the reference. OFFSET requires a reference and positive height and width; omitted dimensions inherit the source shape. Row and column offsets truncate toward zero. Out-of-grid bounds return #REF!; nonpositive dimensions, nonnumeric offsets and a non-reference first argument return #VALUE!. INDIRECT supports A1 by default and R1C1 when a1 is FALSE, including relative R1C1 coordinates, sheet-qualified references, defined names and table references. Invalid reference text and external workbook references return #REF!. Reference text inside INDIRECT is not rewritten by fill, copy or structural edits. Ordinary reference arguments to OFFSET are rewritten. References must stay within the Excel grid; reading beyond the loaded sheet retains the engine's #REF! behavior. Existing range, spill and work limits apply. A reference that creates a runtime dependency cycle returns #CYCLE!. Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
| Dialect | Status |
|---|---|
| Microsoft Excel | divergent |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: A space between two references is the intersection operator. An empty intersection, or references on different sheets, return #NULL!. A non-reference operand returns #VALUE!. Spaces around other operators, after commas, before a closing parenthesis and between a function name and its parenthesis are not operators. A comma inside parentheses that are not a function call is the union operator, as in (A1:A2,C1:C2). Aggregate functions that read their arguments as a list of values accept a union. INDEX picks area area_num of a union, and an area_num past the last area returns #REF!. A union in any other position returns #VALUE!. AREAS counts the areas of a reference: the members of a union, or 1 for a cell, range or intersection. A non-reference argument returns #VALUE!. A 3-D reference such as Sheet1:Sheet3!A1:B2 or 'Sheet 1:Sheet 3'!A1 spans the sheets between its endpoints in workbook tab order. SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, VAR.S and VAR.P accept it; any other position returns #VALUE!. Excel also accepts it in the A-suffixed aggregate functions. A sheet inserted or moved inside the span joins it; a sheet moved out leaves it. Deleting an endpoint moves the endpoint to its inner neighbour; moving an endpoint past the other endpoint drops it; renaming an endpoint rewrites the formula text. Deleting every sheet of the span gives #REF!. Row and column edits do not change a 3-D reference. Cells beyond a smaller sheet's grid read as blank. XLSX import and export keep the Excel formula text of intersections, unions and 3-D references. Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
| Dialect | Status |
|---|---|
| Microsoft Excel | divergent |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: CELL supports the info types address, col, contents, row and type. Other info types return #VALUE!. CELL without a reference describes the cell of the formula, as in LibreOffice. Excel uses the last changed cell. A formula that reads its own cell with FORMULATEXT, ISFORMULA or CELL is a #CYCLE! error. Cells inside a spilled range and host-linked reference cells hold no formula. For them, FORMULATEXT returns #N/A and ISFORMULA returns FALSE. Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
| Dialect | Status |
|---|---|
| Microsoft Excel | divergent |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: HYPERLINK returns its display value: the friendly name, or the link when there is no friendly name. The grid does not open the link. Use cell hyperlinks for links that open. Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/mod.rspackages/wasm/src/eval/functions.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/tests.rspackages/core/test/formula-assist.test.ts |
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/reference.rspackages/wasm/src/eval/dependency.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/tests.rsscripts/conformance/formula-evidence.ts |
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/areas.rspackages/wasm/src/eval/matrix.rspackages/wasm/src/store.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/tests.rspackages/xlsx/test/formula-source-preservation.test.tsscripts/conformance/formula-evidence.ts |
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/info.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/tests.rspackages/core/test/formula-assist.test.ts |