Get a single value from a multi-row function
Wrap a multi-row SheetsFinance function with INDEX or ARRAY_CONSTRAIN to pull only the cell you need.
When you need this
Many SheetsFinance functions return arrays. For example, SF_DIVIDEND returns a table of dates, dividends, and frequencies, and SF_TECHNICAL returns an array of dates, OHLC data and indicator values. If you only want the latest value, or a specific row, use a helper function. These helper functions are in-built in both Google Sheets and Excel and just a few of the many ways you can manipulate arrays of data in your spreadsheet.
Pull the latest value with INDEX()
Our date-based functions default to returning data in descending chronological order (most recent first). This includes functions such as Time series, Dividends and Technicals. Sometime's we only want to return the latest value, for instance the most recent dividend or latest technical indicator (e.g. today's RSI(14)). In these scenarios we can use in the in-built function INDEX() . The INDEX() function let's you return only a single cell from a range of cells by selecting the row and column. In this case, if we want to grab only the first item of an SF function we can wrap it in INDEX(..., 1, 1) like so:
Example: latest RSI(14) for a single cell.
=INDEX(SF_TECHNICAL("AAPL", "14rsi", "daily", $A$1-4, $A$1, "NH"), 1, 1)In the above example, we assume A1 has =TODAY()in it.
Example: latest close from a time series.
=INDEX(SF_TIMESERIES("AAPL", "2024-01-01", "2024-12-31", "daily", "close"), 1, 1)Limit rows with ARRAY_CONSTRAIN()
Use ARRAY_CONSTRAIN when you want a fixed number of rows and columns rather than a single cell.
Example: Latest 2 dividend rows
=ARRAY_CONSTRAIN(SF_DIVIDEND("AAPL", $J$1-90, $J$1), 2, 8)Example: latest dividend row without headers.
=ARRAY_CONSTRAIN(SF_DIVIDEND("AAPL", $J$1-90, $J$1, "", "NH"), 1, 8)Example: only the first three peers.
=ARRAY_CONSTRAIN(SF("AAPL", "peers"), 3, 1)When not to use INDEX
If a function already returns a single value, wrapping it with INDEX can break the formula. For example, =SF("AAPL", "realTime", "price") is already a single cell, so INDEX is unnecessary.
What is next
For more on output formatting, read Function List. For troubleshooting array errors, see Fix common spreadsheet errors.