Indirect Function Help Required

[anishms]

Hi All,

In the below screen shot, I'm referring to a named range called "Function" but the formula gives #REF! error.
Somebody please provide a solution


Thanks in advance

 

[Excel Wizard]

INDIRECT can only refer to normal range name eg ='Clinics Verticlal'!$A$2:$A$99, not a formula

If you need dynamic range, better use Table and avoid volatile function like OFFSET and INDIRECT

=UNIQUE(INDEX(Table1,,MATCH(D3,Table1[#Headers],)))

 

[anishms]

T

INDIRECT can only refer to normal range name eg ='Clinics Verticlal'!$A$2:$A$99, not a formula

If you need dynamic range, better use Table and avoid volatile function like OFFSET and INDIRECT

=UNIQUE(INDEX(Table1,,MATCH(D3,Table1[#Headers],)))

Thanks for the clarification, I didn't use table because I may need to marge some of the cells in the data

 

[Peter Bartholomew]

I fully concur with @Excel Wizard . You have sacrificed too much for the dubious option of merging a few cells, starting with needing to employ computationally expensive and largely obsolete methods of generating dynamic ranges.

I would tend to use XLOOKUP rather than the INDEX/XMATCH combination to return a field from a table but there is not much to choose
= UNIQUE( XLOOKUP(D3, Table1[#Headers], Table1) )

If you really do wish to persist with your present strategy, the Name "function" can be calculated using the old EVALUATE() function. The catch is that EVALUATE does not work on the grid; it must be called from a formula within a defined name.

 

[anishms]

I fully concur with @Excel Wizard . You have sacrificed too much for the dubious option of merging a few cells, starting with needing to employ computationally expensive and largely obsolete methods of generating dynamic ranges.

I would tend to use XLOOKUP rather than the INDEX/XMATCH combination to return a field from a table but there is not much to choose
= UNIQUE( XLOOKUP(D3, Table1[#Headers], Table1) )

If you really do wish to persist with your present strategy, the Name "function" can be calculated using the old EVALUATE() function. The catch is that EVALUATE does not work on the grid; it must be called from a formula within a defined name.

Thanks for your suggestions, I will make a version with table and prefer to use that if no merging is used