Returning Cell References
This capability is not obvious because cell references are implicitly converted to cell values.
Functions that can return cell references
| CHOOSE | Returns the value in a row (or column) based on an index number. |
| DROP | Returns the array without certain rows or columns. |
| IF | Returns the value based on whether a condition is True or False. |
| INDEX | Returns the value from a cell range which is the intersection of a row AND a column. |
| OFFSET | Returns the value in a cell which is an offset from another cell. |
| TAKE | Returns the intersection of specific rows and columns in an array. |
| XLOOKUP | Returns the value in the same row after finding a matching value in any column. |
| CHOOSE Returns the value in a row (or column) based on an index number. |
| DROP Returns the array without certain rows or columns. |
| IF Returns the value based on whether a condition is True or False. |
| INDEX Returns the value from a cell range which is the intersection of a row AND a column. |
| OFFSET Returns the value in a cell which is an offset from another cell. |
| TAKE Returns the intersection of specific rows and columns in an array. |
| XLOOKUP Returns the value in the same row after finding a matching value in any column. |
Functions that cannot return cell references
It is worth stating that the following functions are not capable of returning cell references.
| CHOOSECOLS | Returns the array with just a specific number of columns. |
| CHOOSEROWS | Returns the array with just a specific number of rows. |
| EXPAND | Returns the array expanded or padded to specific dimensions. |
| HLOOKUP | Returns the value in the same column after finding a matching value in the first row. |
| VLOOKUP | Returns the value in the same row after finding a matching value in the first column. |
| CHOOSECOLS Returns the array with just a specific number of columns. |
| CHOOSEROWS Returns the array with just a specific number of rows. |
| EXPAND Returns the array expanded or padded to specific dimensions. |
| HLOOKUP Returns the value in the same column after finding a matching value in the first row. |
| VLOOKUP Returns the value in the same row after finding a matching value in the first column. |
© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited TopPrevNext