Dynamic Table Name in Excel Lambda Function with Structured References without INDIRECT
Matthew Martinez 
Is it possible to define an excel lambda function that takes a parameter that acts as a "dynamic" name for a table, that is then used in a structured reference, without using the INDIRECT function?
My hope is to be able to define a helper method that allows users to, in effect, use a table to define a dictionary structure with a string as the key and a tuple as the value, and then, to pluck out specific pieces of that tuple.
An example table named "Variables" is below:
| Variable | Initial Value | Initial Unit |
|---|---|---|
| Pressure | 1 | atm |
| Volume | 4 | L |
| mass | 10 | g |
| Temperature | 30 | C |
| Rgas | 0.7 | atm*L/g*C |
And my desired behaviour is to be able to define a lambda that users can then call like so:
=l.Variable("Variables","mass","Initial Value")
(with the result being 10)
So far though, I can only achieve this particular interface by employing the INDIRECT function:
=LAMBDA(Table,Var,Prop, INDEX( INDIRECT(Table & "[#All]"), MATCH(Var,INDIRECT(Table & "[[#All],[Variable]]"),0), MATCH(Prop,INDIRECT(Table & "[#Headers]"),0) )
)If I instead want to avoid usage of INDIRECT, I have to hard-code the specific table name:
=LAMBDA(Var,Prop, INDEX( Variables[#All], MATCH(Var,Variables[[#All],[Variable]],0), MATCH(Prop,Variables[#Headers],0) )
)Am I out of luck? Is there a better way to do what I'm trying to do?
I have linked to a workbook that uses both the hard-coded approach and the INDIRECT approach below:
2 Answers
takes a parameter that acts as a "dynamic" name for a table
The best way in Excel to convert a text parameter to a range is to use INDIRECT function.
(other ways are OFFSET and custom VBA function, however in your case both would be more complicated)
However you can still improve your formula using FILTER, instead of INDEX and MATCH.
Below example shows only part of your desired formula as my Excel doesn't yet have LAMBDA.
=FILTER(INDIRECT(E1&"["&G1&"]"),INDIRECT(E1&"[Variable]")=F1)
Non-volatile method to INDEX into different Tables or Sheets
The only way to INDEX into different tables using a text variable is to use INDIRECT as per the question. The reason to avoid INDIRECT of course is because it is volatile, meaning it will recalculate at every possible opportunity (INDIRECT can refer to any cell, and is thus dependent on every cell). Volatile functions can impact performance, especially for large workbooks.
For a finite predefined number of tables or sheets you can use CHOOSE and LET (LET requires Excel 365). For tables:
=LET(tableall,CHOOSE(MATCH(table,{"Table1","Table2","Table3"},0),Table1[#All],Table2[#All],Table3[#All]),INDEX(tableall,MATCH(var,INDEX(tableall,,1),0),MATCH(prop,INDEX(tableall,1,),0)))Where var is the lookup value in the first column of the table, and prop is the matching header column.
If you need specific table columns your can extend LET with multiple variables e.g.:
=LET(tableindex,MATCH(table,{"Table1","Table2","Table3"},0),tableall,CHOOSE(tableindex,Table1[#All],Table2[#All],Table3[#All]),tablehdr,CHOOSE(tableindex,Table1[#Headers],Table2[#Headers],Table3[#Headers]),INDEX(tableall,MATCH(var,INDEX(tableall,,1),0),MATCH(prop,tablehdr,0)))Or if you only need a VLOOKUP and not a full INDEX:
=LET(tableall,CHOOSE(MATCH(table,{"Table1","Table2","Table3"},0),Table1[#All],Table2[#All],Table3[#All]),INDEX(tableall,VLOOKUP(var,tableall,MATCH(prop,INDEX(tableall,1,),0),FALSE))And lastly once LAMDA makes it into the production version of Excel 365, you can basically define the above as a named range function and call it with parameters e.g. =TABLEINDEX("Table1","Mass","Initial Value")
And as an FYI, you can do the same to dynamically address different Sheets in a non-volatile way:
=LET(sheet,CHOOSE(MATCH(sheetname,{"Sheet1","Sheet2","Sheet3"},0),Sheet1!$1:$10000,Sheet2!$1:$10000,Sheet3!$1:$10000),INDEX(sheet, i, j))where sheetname is the sheetname as a textvalue, i and j are row and column indexes.
PS. If you don't have Excel 365, the above can all be done without LET, you just need to substitute/duplicate the formulae for each named LET variable in the main formula (more computationally intensive).
