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 (cell or range) in a row (or column) based on an index number.
DROP	Returns the array (or range) without certain rows or columns.
IF	Returns the value (cell or range) based on whether a condition is True or False.
INDEX	Returns the value (cell or range) from a cell range which is the intersection of a row AND a column.
OFFSET	Returns the value (cell or range) in a cell which is an offset from another cell.
TAKE	Returns the array (or range) from taking the intersection of specific rows and columns.
XLOOKUP	Returns the value (cell or range) in the same row after finding a matching value in any column.

CHOOSE Returns the value (cell or range) in a row (or column) based on an index number.
DROP Returns the array (or range) without certain rows or columns.
IF Returns the value (cell or range) based on whether a condition is True or False.
INDEX Returns the value (cell or range) from a cell range which is the intersection of a row AND a column.
OFFSET Returns the value (cell or range) in a cell which is an offset from another cell.
TAKE Returns the array (or range) from taking the intersection of specific rows and columns.
XLOOKUP Returns the value (cell or range) in the same row after finding a matching value in any column.

"=COUNTIFS(CHOOSE(2,A4:A6,B4:B6),100)" evaluates to "=COUNTIFS(B4:B6,100)" 
"=SUM(CHOOSE(2,A4,B4):B6)" evaluates to "=SUM(B4:B6)"

Lambda Functions that can return cell references

BYCOL	Returns the results from applying a LAMBDA function to each column in an array.
BYROW	Returns the results from applying a LAMBDA function to each row in an array.
MAP	Returns the array formed by mapping each value to a new value using a LAMBDA function.
REDUCE	Returns the total value after reducing an array by applying a LAMBDA function.
SCAN	Returns the array after applying a LAMBDA function to each value and returns an array.

BYCOL Returns the results from applying a LAMBDA function to each column in an array.
BYROW Returns the results from applying a LAMBDA function to each row in an array.
MAP Returns the array formed by mapping each value to a new value using a LAMBDA function.
REDUCE Returns the total value after reducing an array by applying a LAMBDA function.
SCAN Returns the array after applying a LAMBDA function to each value and returns an array.

"=BYROW(A2:A6,LAMBDA(n,ADDRESS(ROW(n),COLUMN(n))))" 
"=MAP(A2:A6,LAMBDA(n,ADDRESS(ROW(n),COLUMN(n))))" 
"=REDUCE("",A2:A6,LAMBDA(a,n,a & ADDRESS(ROW(n),COLUMN(n))))" 
"=SCAN("",A2:A6,LAMBDA(a,n,a & ADDRESS(ROW(n),COLUMN(n))))"

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.