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.
- 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.
The value to be evaluated using implicit intersection.
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.
- 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.
If the rows and columns don’t intersect, or there are more than one intersections, the function returns a #VALUE! error.