Forum Discussion
Dynamic Let Function
I think what you want is to have the "index_num" to be an array like: XMATCH(item_array,item_array)
but the problem is that it is a column of values and the 'item_array' is a column so it will only show the one value.
In the attached I created a version with XMATCH(TOROW(item_array),item_array), and that does produce the array output but because the other values are single values it repeats them for the 'array' so the grid is 'ugly'.
Since you are manually adding the list of variables why not just spit them out using something like:
IFERROR(VSTACK({"a","b","c","d","e","f","item","item_array","index_num"},
HSTACK(a,b,c,d,e,f,item,item_array,index_num)),""))
But then maybe I don't understand your goal.
Hey m_tarler. Thanks.
Your formula IFERROR(VSTACK({"a","b","c","d","e","f","item","item_array","index_num"},
HSTACK(a,b,c,d,e,f,item,item_array,index_num)),"")) would indeed show each variable and value in excel but the use case I'm interested is slightly different.
What the current version allows is you to point the LET formula to a cell that specifies the item you want and it returns what was define in the formula. Say you had a long calculation with lots of intermediate steps and you wanted to pull out just one of the outputs from the intermediate steps. This lets you do that easily. If I understand your formula, it returns all the variables and all the outputs rather than a specific one. I also think it requires retyping all the variable names in the cumbersome quotation and comma format which I've found prone to errors for my fat fingers. My formula has to type the variable names three times, first in the definitions, then as the text output of labels and then as the second argument in CHOOSE. It is error prone (missing comma, closed quote, typos, etc). Was trying to figure out a way to type it once and reference it.