Passing a Table as a variable (in a LAMBDA() function)

[ColinJHarrison]

Hi,

Why can't I do this (in Excel 356)? I have a table called Table

I'm trying to build a LAMBDA() function that will contain something like the following (this is the test syntax...):

=LAMBDA(T,T[#Headers])(Table)

But the T[#Headers] statement is obviously just flat out illegal, because the editor won't even accept it! ("There is a problem with this formula..."). Why is that? How can I construct a reference to a Table that will allow me to pass the Table as a variable and then grab a range from it?

Thanks.

[#2]

@ColinJHarrison In that formula the first T is equivalent to the table array called T. Enter =Table somewhere and the entire table excluding the headers will be spilled.

The second T combined with [#Headers] merely refers to the table name. So, the LAMBDA needs to concatenate the table name with [#Headers] and use INDIRECT to reference it.

This is demonstrated in the picture below.

A1 contains a formula referencing the top left cell of the table.

A2 extracts the table name (I named the cell "TableName").

A4:B7 contains the table.

A9 shows what T would do.

A13 is the LAMBDA based on the table name.

[#3]

@Riny_van_Eekelen 

Hi Riny (are you the only person on this forum? Haha)

I had tried INDIRECT(), but it doesn't give me the expected result.

If I have a table called Table1, and I call =Table1[#Headers], (as I have in cell D10 in the attached image/sheet) then I get the header row values spilling horizontally from D10, as I would expect.

I would have thought that calling =LAMBDA(Tn,INDIRECT(Tn&"[#Headers]"))(Table1) should do exactly the same thing, but it doesn't. What I get is a spill range the same dimensions as the whole Table, filled with #REF! errors, which are "Invalid Cell Reference" errors. See the screenshot below, in which D4 contains the LAMBDA() call shown in red in D2.

Just in case it was an issue with the LAMBDA() test syntax, I assigned the function to a name (TPX()) in the Name Manager and called it directly from cell G4 (=TPX(Table1)), which as you can see, has exactly the same result.

I am perplexed. I have attached the sheet below. 

Cheers,

Colin

[#4 · Accepted solution]

@ColinJHarrison I think the point you are missing is that Table1 may refer to the table itself as a dynamic array and to the name of the table which is no more than a text. The LAMBDA needs to refer to the table name.

Remove the quotes in A2 and change the formula to this:

=LAMBDA(Tn,INDIRECT(Tn&"[#Headers]"))(A2)

Similar for the named LAMBDA function. You want to feed it the text that is in A2, so

=TPX(A2) will work.

See attached.

[#5]

OK. That makes sense. I guess I was thinking I needed to pass a ref to the actual dynamic range rather than just to its name. But of course that wouldn't make sense for an INDIRECT() call, eh... Hm. In any case, I can see how to make what I'[m trying to do work now, so, once again, thank you for your assistance!

[#6]

@ColinJHarrison 

I'm trying to do the same thing, pass a table to a lambda function.  But I would greatly prefer a solution without the use of INDIRECT as it slows calculations way down as it is volatile.  (Calculates every time anything changes.) 

Anyone have a solution for passing a table to a lambda function that does not use INDIRECT?

[#7]

Ok, figured it out, use INDEX instead of INDIRECT. Here is an example that returns the data from a column in a table:
=LAMBDA(TableAll,ColumnTitle,DROP(INDEX(TableAll,0,MATCH(ColumnTitle,INDEX(TableAll,1,0),0)),1,))(Table1[#All],"Cost")
Found a useful discussion:
https://superuser.com/questions/1713772/dynamic-table-name-in-excel-lambda-function-with-structured-...