SINGLE

Formulas / SINGLE
The SINGLE function is used to extract a single value from an array or range, making it particularly useful for retrieving a specific value from a dynamic array result.
=SINGLE(array)
  • array - The array or range from which to extract a single value.

Examples

  • =SINGLE(A1:A5)

    This formula extracts a single value from the range A1:A5. If the formula is entered in a cell within rows 1 to 5, it returns the value of the corresponding cell in column A. If the formula is entered outside of rows 1 to 5, it returns a #VALUE! error.

  • =SINGLE(B2)

    This formula returns the value of cell B2, as the range contains only one cell. The SINGLE function is used here to extract the value of a single cell, which can be useful when working with dynamic arrays.


Summary

The SINGLE function returns the value of a single cell in a range, and is primarily used with dynamic arrays to extract a single value from an array result.

  • The SINGLE function is a dynamic array function that extracts a single value from an array or range, making it useful for retrieving specific values from dynamic array results.
  • SINGLE returns the value of the cell at the intersection of the row and column in which the formula is entered when applied to a range with multiple cells; if there is no intersection, it returns a #VALUE! error.
  • The SINGLE function can be used with ranges containing text, non-numeric values, or numeric values, and it extracts a single value regardless of the data type.

Frequently Asked Questions

What does the SINGLE function do?
The SINGLE function extracts a single value from an array or range. It is especially useful when working with dynamic arrays to extract a specific value from an array result returned by another formula.
When should I use the SINGLE function?
You should use the SINGLE function when you need to extract a single value from an array or range, particularly when dealing with dynamic arrays. SINGLE is commonly used in scenarios where a formula returns an array, but you only need one specific value from that array.
What happens if the SINGLE function is applied to a range with multiple cells?
If the SINGLE function is applied to a range with multiple cells, it returns the value of the cell at the intersection of the row and column in which the formula is entered. If there is no intersection, the function returns a #VALUE! error.
Can the SINGLE function be used with ranges containing text or non-numeric values?
Yes, the SINGLE function can be used with ranges containing text, non-numeric values, or numeric values. It extracts a single value from the specified range, regardless of the data type of that value.
What error does SINGLE return if there is no intersection between the formula cell and the specified range?
SINGLE returns a #VALUE! error if there is no intersection between the cell in which the formula is entered and the specified range.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV