SINGLE
Returns a single value from its argument by implicit intersection.
SINGLE(x) is the stored form of the implicit-intersection operator @x
(files written by Excel store _xlfn.SINGLE(x)). Both evaluate the same
way: a reference, written or returned by a function such as INDEX or
OFFSET, selects the cell on the formula's row (for a column) or column
(for a row), and a computed array gives its top-left value.
The Formualizer engine in this repository at 0.14.0 plus unreleased changes computes the example results. Known differences from Excel are listed on this page.
Syntax
=SINGLE(value)
Arguments
| Argument | Type | Required | Default |
|---|---|---|---|
value | any (range) | Required | — |
The examples enter the formula in cell CV100.
Remarks
- A multi-row, multi-column reference selects the cell at the formula's row and column.
- The result never spills: an array constant or an array-returning
function such as
SEQUENCEgives only its top-left value.
Error cases
#VALUE!when the formula cell is outside the reference's rows or columns.
Examples
Intersect a single-cell reference with the formula context.
Formula =SINGLE(A100)
| A | |
|---|---|
| 100 | 42 |
Expected result 42
This example uses behavior that differs in the published browser package.
A scalar expression can be returned as a single value.
Formula =SINGLE(42)
Expected result 42
This example uses behavior that differs in the published browser package.
Intersect a vertical range with the formula's row.
Formula =SINGLE(A100:A101)
| A | |
|---|---|
| 100 | 42 |
Expected result 42
This example uses behavior that differs in the published browser package.
The example formula's row is outside this vertical range.
Formula =SINGLE(A1:A2)
| A | |
|---|---|
| 1 | 10 |
| 2 | 20 |
Expected result #VALUE!
This example uses behavior that differs in the published browser package.
Return the top-left element of a two-by-two array.
Formula =SINGLE({1,2;3,4})
Expected result 1
This example uses behavior that differs in the published browser package.
Related functions
FAQ
How does SINGLE relate to @?
They are the same operation: @A1:A10 is written as _xlfn.SINGLE(A1:A10) in stored files, and both return one value by implicit intersection.