Exceljet

Quick, clean, and to the point

Excel SINGLE Function

Excel SINGLE function
Summary 

The Excel SINGLE function returns a single value using implicit intersection. The SINGLE function was originally introduced with Dynamic Arrays, but later deprecated in favor of the @ character.

Purpose 
Get single value with implicit intersection
Return value 
Cell or value at implied intersection
Syntax 
=SINGLE (value)
Arguments 
  • value - Value to evaluate using implicit intersection.
Version 
Usage notes 

The Excel SINGLE function returns a single value using implicit intersection. The SINGLE function is primarily for backwards compatibility with implicit intersection formulas in earlier versions of Excel.

When the supplied argument is a range, SINGLE will return the cell at the intersection of the row or column of the formula cell. Where there is no intersection, or more than one intersection, SINGLE returns a #VALUE! error.

In the example shown, the following formula in D6 "spills" and returns all values in the range B4:B8:

=B4:B8

When the single function is wrapped around the same range, SINGLE returns 3, which corresponds to cell B6, which intersects with the formula in cell D6:

=SINGLE(B4:B8)
The SINGLE function was originally introduced with Dynamic Arrays, but later deprecated in favor of the @ character.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.