2. Programming Q&A
  3. INDIRECT array to refer to a table column not working with array formulas - Stack Overflow

INDIRECT array to refer to a table column not working with array formulas - Stack Overflow

admin2025-04-26  4

Premise

I've seen more questions regarding the use of the INDIRECT function in Excel with array functions, but my situation is a bit different and doesn't seem to allow the use of any INDEX or OFFSET function, as often suggested...

Situation

I am currently referring to a table column, where the table name as well as the column name are dynamically given as string parameters, using the following formula:

GetDesiredColumn = LAMBDA(TableName, ColumnName, INDIRECT(TableName & "[" & ColumnName & "]"))

If I write in cell A1 the command

=GetDesiredColumn("Table_Students", "Age")

everything works fine and the data displays correctly (with the "blue spill contour").

My problem

Now, the following formula (as an example, to display the number of students of any age) works correctly:

COUNT.IF(A1#, SEQUENCE(100) )

However, the formula

COUNT.IF(GetDesiredColumn("Table_Students", "Age"), SEQUENCE(100) )

does NOT work: the array returned by the INDIRECT function does not work directly, but only if firstly referred after being called in the cell A1...

Is there a way to modify the GetDesiredColumn function to make it return a dynamic array that correctly works/spills in formulas with dynamic arrays?

Thank you very much for any suggestion!

Premise

I've seen more questions regarding the use of the INDIRECT function in Excel with array functions, but my situation is a bit different and doesn't seem to allow the use of any INDEX or OFFSET function, as often suggested...

Situation

I am currently referring to a table column, where the table name as well as the column name are dynamically given as string parameters, using the following formula:

GetDesiredColumn = LAMBDA(TableName, ColumnName, INDIRECT(TableName & "[" & ColumnName & "]"))

If I write in cell A1 the command

=GetDesiredColumn("Table_Students", "Age")

everything works fine and the data displays correctly (with the "blue spill contour").

My problem

Now, the following formula (as an example, to display the number of students of any age) works correctly:

COUNT.IF(A1#, SEQUENCE(100) )

However, the formula

COUNT.IF(GetDesiredColumn("Table_Students", "Age"), SEQUENCE(100) )

does NOT work: the array returned by the INDIRECT function does not work directly, but only if firstly referred after being called in the cell A1...

Is there a way to modify the GetDesiredColumn function to make it return a dynamic array that correctly works/spills in formulas with dynamic arrays?

Thank you very much for any suggestion!

  • excel-formula
Share Improve this question edited Jan 22 at 21:03 marc_s 757k184 gold badges1.4k silver badges1.5k bronze badges asked Jan 13 at 16:34 Mattia BergomiMattia Bergomi 351 silver badge6 bronze badges 7
  • 2 All %IF(S) Functions do not allow arrays in the area criteria. They require Ranges. So the problem is the formula using your array, not the array itself. You will need to use some other function or grouping of functions, Like REDUCE(LAMBDA) to get the count. – Scott Craner Commented Jan 13 at 16:40
  • 1 Can use TRIMRANGE() function, that would work: =COUNTIF(TRIMRANGE(GetDesiredColumn("Table_Students", "Age")),SEQUENCE(100)) – Mayukh Bhattacharya Commented Jan 13 at 17:17
  • 2 What language is COUNT.IF (with a dot)? =COUNTIF(GetDesiredColumn("Table_Students","Age"),SEQUENCE(100)) works perfectly on my system (Windows 10, English, Office 365 for Business, Current Channel, 64-bit) using the same custom function definition you've shared. The INDIRECT function returns a range object (reference), not an array. For example, =ISREF(GetDesiredColumn("Table_Students","Age")) should return TRUE; if it returns FALSE, there must be something else at play that is causing your function to return an array object. – DjC Commented Jan 14 at 3:21
  • 1 Thank you, TRIMRANGE solved the problem! – Mattia Bergomi Commented Jan 14 at 8:47
  • 1 For more information, see: Excel Functions that Return References; however, this site is a bit dated (posted in June of 2020) and should be updated to include both TAKE and DROP, as well as the new TRIMRANGE function (still in beta). – DjC Commented Jan 16 at 5:18
 |  Show 2 more comments

2 Answers 2

Reset to default 1

Try

GetDesiredColumn = LAMBDA(Table, ColumnName, INDEX(Table, ,XMATCH(ColumnName, INDEX(Table, 1))))

This assumes Table is a range and that the first row of the range is the header.

As already explained by Scott Craner Sir, any IFs family functions don't work with Arrays, even if you refer the syntax in MSFT Documentation, it shows the syntax starts with Criteria Range and not says Arrays like other DAF functions, this also explains why those functions dont work when the workbook is closed and returns #REF! error.

However, if you have access to TRIMRANGE() function then can make this workaround:

• Formula used in cell D2

=COUNTIF(TRIMRANGE(GetDesiredColumn("Table_Students","AGE")),SEQUENCE(25))

Formula E2 shows for the Table_Men

You can also, use this way in the name manager:

GetDesiredColumn =  LAMBDA(TableName,ColumnName, TRIMRANGE(INDIRECT(TableName & "[" & ColumnName & "]")))

and use with COUNTIF() as

=COUNTIF(GetDesiredColumn("Table_Students","AGE"),SEQUENCE(25))

Documentation of TRIMRANGE() --> Read Here

转载请注明原文地址:http://anycun.com/QandA/1745636937a91039.html

INDIRECT array to refer to a table column not working with array formulasStack Overflow