Which formulas can return a cell reference instead of its value?

[excelos]

Hello

Which formulas can return a cell reference instead of its value?

I know INDEX can do that, any other?

I want to particularly know the formulas that I can use them in a way of SUM(FORMULA():A2) etc

Thanks!

 

[6StringJazzer]

That's a fairly broad request. It would help to know what you are trying to calculate.

Offhand the ones I can think of are:

OFFSET
INDIRECT

Also UDFs can be written to return whatever you want, including a range.

 

[excelos]

Thanks but unfortunately both of these are volatile thus a no-go. Any other? Or any other way to parse a lookup/calculation as a cell reference?

 

[RoryA]

Those are pretty much your options.

 

[Alex Blakenburg]

Xlookup is another

 

[Alex Blakenburg]

I think this might demonstrate what you are referring to for both Index & XLookup ie the return value is a cell reference that can be used in another function such as Sum.

20220404 Functions that return an address.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Index	Amt				From	To	Total	Function	Formula
2	a	1000			Sum	c	e	60	<-- Index	=SUM(INDEX($B$2:$B$8,MATCH(F2,$A$2:$A$8,0),0):INDEX($B$2:$B$8,MATCH(G2,$A$2:$A$8,0),0))
3	b	2000						60	<-- XLookup	=SUM(XLOOKUP(F2,$A$2:$A$8,$B$2:$B$8):(XLOOKUP(G2,$A$2:$A$8,$B$2:$B$8)))
4	c	10
5	d	20
6	e	30
7	f	4000
8	g	5000
Test

Cell Formulas
Range	Formula
H2	H2	=SUM(INDEX($B$2:$B$8,MATCH(F2,$A$2:$A$8,0),0):INDEX($B$2:$B$8,MATCH(G2,$A$2:$A$8,0),0))
H3	H3	=SUM(XLOOKUP(F2,$A$2:$A$8,$B$2:$B$8):(XLOOKUP(G2,$A$2:$A$8,$B$2:$B$8)))
J2:J3	J2	=FORMULATEXT(H2)

And in VBA to show both function can have the "Address" Property.

Sub FunctionsReturningAddress()

    Debug.Print "Index Address " & Application.Index(Range("$B$2:$B$8"), Application.Match(Range("F2").Value, Range("$A$2:$A$8"), 0), 0).Address
  
    Debug.Print "XLookup Address " & Application.XLookup(Range("F2").Value, Range("$A$2:$A$8"), Range("$B$2:$B$8")).Address

End Sub

Which shows in the immediate window
Index Address $B$4
XLookup Address $B$4