CODEWITHSUNDEEP
excel-formula
Mattia Bergomi
Mattia Bergomi

Reputation: 23

INDIRECT array to refer to a table column not working with array formulas

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!

Upvotes: 0

Views: 86

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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:

enter image description here

• 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

Upvotes: 0

z..
z..

Reputation: 12873

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.

Upvotes: 1

Related Questions