SINGLE Function

by | Feb 20, 2019 | Excel Formulas

single function

The Excel SINGLE function is a Lookup & Reference formula that returns a value using implicit intersection. In this guide, we’re going to show you how to use the Excel SINGLE function and also go over some tips and error handling methods.

The Dynamic Array functions can populate an array of values in a range of cells based on a formula. This behavior is called spilling and can help overcome the limitations of array formulas.

Supported versions

  • At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.

Syntax

=SINGLE(value)

Arguments

value

The value to be evaluated using implicit intersection.


Example

=SINGLE(B3:B6)
formula is a sample use case with this function. When you enter this formula (i.e. cell D4) it returns the string Blastoise. The return value would be Pikachu if you entered the formula to the cell in row 5.

If you use the same reference without the SINGLE function, Excel will spill the contents of the array into the following cells. The following example shows how Excel calculates the same range with and without the SINGLE function.

Excel automatically changes the SINGLE function with the @ character. You can use this notation instead of typing in the function. For example, =SINGLE(B3:B6) versus @B3:B6

Download Workbook

Tips

  • The Excel SINGLE function is backwards compatible with existing implicit intersection formulas.
  • Implicit Intersection is an Excel feature that allows entering the range references inside formulas which require a single value. When a range is entered, Excel performs the calculations with a value in the range that shares the same row or column with the formula cell itself. For example, if you enter =D3:D6 + 10 into A4, Excel will calculate D4 + 10. The advantage of this method is that you can enter the same formula into the A3:A6 range and get different returns based on rows.

Issues

#VALUE!

If the rows and columns don't intersect, or there are more than one intersections, the function returns a #VALUE! error.