Feb 25 2022 04:22 PM
Hello,
Thanks to any one who can help. I Have a Let formula that pulls data from 3 separate sheets and stacks it in one column on a separate sheet. I also want to add another variable to the let formula that uses xlookup on the first array. Basically its a list of names it would display a rate that's associated with that name from another sheet and the final result of the let statement would display 2 columns original being the names and second being the rate. The following is the let formula that pulls the names. Its the addition of the second column that I'm having issues with any help would be appreciated.
=LET(
List,SORT(IF(ISBLANK(EducationData!B72:B239),"Empty",EducationData!B72:C239),1,1),
List2,SORT(IF(ISBLANK(AdminData!B4:C35),"Empty",AdminData!B4:C35),1,1),
List3,SORT(IF(ISBLANK(TherapiesData!C87:D291),"Empty",TherapiesData!C87:D291),1,1),
Fltr,CONCATENATE(FILTER(FILTER(List,INDEX(List,0,1)<>"Empty"),{1,0}),", ",FILTER(FILTER(List,INDEX(List,0,1)<>"Empty"),{0,1})),
Fltr2,CONCATENATE(FILTER(FILTER(List2,INDEX(List2,0,1)<>"Empty"),{1,0}),", ",FILTER(FILTER(List2,INDEX(List2,0,1)<>"Empty"),{0,1})),
Fltr3,CONCATENATE(FILTER(FILTER(List3,INDEX(List3,0,1)<>"Empty"),{1,0}),", ",FILTER(FILTER(List3,INDEX(List3,0,1)<>"Empty"),{0,1})),
NumCols, COLUMNS(Fltr),
RowsA, ROWS(Fltr),
RowsB, ROWS(Fltr2),
RowsC, ROWS(Fltr3),
SeqCol, SEQUENCE(RowsA+ RowsB+ RowsC),
SeqRow, SEQUENCE(1, NumCols),
Result, IF(SeqCol <= RowsA, INDEX(Fltr,SeqCol, SeqRow),IF(SeqCol <= (RowsA + RowsB),INDEX(Fltr2,SeqCol - RowsA, SeqRow),INDEX(Fltr3,SeqCol-(RowsA + RowsB),SeqRow))),
Result
)
Feb 25 2022 07:14 PM
Have to say, first, that what you've written is the most impressive LET formula I've ever seen. That you're having trouble in the aftermath of successfully doing that first step is, in itself, daunting.
I wonder, though, why you think it necessary to incorporate an XLOOKUP into that already impressive LET?
Could XLOOKUP not work simply--cleanly, clearly, and (incidentally) more readily maintainable--as the sole formula in that second column? There's no Excel commandment that I'm aware of that encourages us to put all the functionality into a single formula. LET is powerful, yes, but there's value in simplicity too.
Feb 28 2022 09:36 AM
Feb 28 2022 11:13 AM - edited Feb 28 2022 11:15 AM
Unless I'm mistaken, XLOOKUP in the second column would work in real-time (i.e., dynamically) based on what's in the first column. Just embed it in an IFERROR and copy it down to more rows than you expect, and have it produce a blank if it doesn't produce a value based on the lookup.
Feb 28 2022 11:56 AM
Feb 28 2022 02:18 PM
I might have found away. but i have hit a little snag. Does anyone know how to reference a column inside an array in a let statement?
=LET(
List,CHOOSE({1,2,3,4,5,6,7},EducationData!B72:B239,EducationData!C72:C239,EducationData!E72:E239,EducationData!F72:F239,EducationData!G72:G239,EducationData!H72:H239,EducationData!J72:J239),
ListEmp,SORT(IF(ISBLANK(List),"Empty",List),1,1),
ListEmp)
In the ListEmp Variable where it references the list variable. How do I specify a specific column instead of the whole array.
Thanks
Feb 28 2022 02:41 PM
For example:
=LET(
List,CHOOSE({1,2,3,4,5,6,7},EducationData!B72:B239,EducationData!C72:C239,EducationData!E72:E239,EducationData!F72:F239,EducationData!G72:G239,EducationData!H72:H239,EducationData!J72:J239),
ListEmp,SORT(IF(ISBLANK(List),"Empty",List),1,1),
ListCol,INDEX(ListEmp,,3),
ListCol)
Feb 28 2022 02:58 PM
Feb 28 2022 03:12 PM
Not sure what you mean.
The syntax of CHOOSE is CHOOSE(index, value1, value2 ,...)
CHOOSE({1,2}, array1, array2) works, but CHOOSE({1,2}, array) doesn't.
Feb 28 2022 03:47 PM
Thanks everyone. I was able to get it to pull and display correctly the following is the final Let Statement.
=LET(
List,SORT(IF(ISBLANK(EducationData!B72:B239),"Empty",EducationData!B72:J239),1,1),
List2,SORT(IF(ISBLANK(AdminData!B4:B35),"Empty",AdminData!B4:L35),1,1),
List3,SORT(IF(ISBLANK(TherapiesData!C87:C291),"Empty",TherapiesData!C87:M291),1,1),
Fltr,FILTER(List,INDEX(List,0,1)<>"Empty"),
Fltr2,FILTER(List2,INDEX(List2,0,1)<>"Empty"),
Fltr3,FILTER(List3,INDEX(List3,0,1)<>"Empty"),
FltrCol,CHOOSE({1,2,3,4,5,6,7},INDEX(Fltr,,1),INDEX(Fltr,,2),INDEX(Fltr,,5),INDEX(Fltr,,4),INDEX(Fltr,,6),INDEX(Fltr,,7),INDEX(Fltr,,8)),
FltrCol2,CHOOSE({1,2,3,4,5,6,7},INDEX(Fltr2,,1),INDEX(Fltr2,,2),INDEX(Fltr2,,5),INDEX(Fltr2,,10),INDEX(Fltr2,,7),INDEX(Fltr2,,8),INDEX(Fltr2,,11)),
FltrCol3,CHOOSE({1,2,3,4,5,6,7},INDEX(Fltr3,,1),INDEX(Fltr3,,2),INDEX(Fltr3,,5),INDEX(Fltr3,,3),INDEX(Fltr3,,7),INDEX(Fltr3,,8),INDEX(Fltr3,,10)),
NumCols, COLUMNS(FltrCol),
RowsA, ROWS(FltrCol),
RowsB, ROWS(FltrCol2),
RowsC, ROWS(FltrCol3),
SeqCol, SEQUENCE(RowsA+ RowsB+ RowsC),
SeqRow, SEQUENCE(1, NumCols),
Result, IF(SeqCol <= RowsA, INDEX(FltrCol,SeqCol, SeqRow),IF(SeqCol <= (RowsA + RowsB),INDEX(FltrCol2,SeqCol - RowsA, SeqRow),INDEX(FltrCol3,SeqCol-(RowsA + RowsB),SeqRow))),
Result
)
Mar 01 2022 04:53 AM
With such problems, what is best depends on what is intended calculation. If it is just a case of appending the tables, I have an add-in from Charles Williams that does the append tables in one step.
= VSTACK("",Table1,Table2,Table3)
Without such a function, life gets a bit harder. I tend to build the solution step by step using Lambda functions. A starting point might be a function to return a Table by index. As an aside, I regard the use of direct referencing as something of an 'own goal'. If the range is oversized, one has the extra task of removing blank records. If the size is not sufficient, it represents a Latent Error. That is a calculation that gives the correct results for now but, as things go on, may eventually cause an error if the range is not increased to match the data.
"Read a table:" Tableλ
= LAMBDA(dept,
CHOOSE(dept,
Table1,
Table2,
Table3)
)
"Return row count for table:" Rowsλ
= LAMBDA(dept, ROWS(Tableλ(dept)))
"Return a single record by table and row indices:" Recordλ
= LAMBDA(department,recordnum,
INDEX(Tableλ(department),recordnum,0))
The awkward steps are accumulating the table row counts so that each output row can be mapped back to a table and record by index.
= LET(
nRows, MAP({1;2;3}, LAMBDA(dept,Rowsλ(dept))),
accRows, SCAN(0,nRows,LAMBDA(tRows,n,tRows+n)) - nRows,
totalRows, SUM(nRows),
MAKEARRAY(totalRows,nCol,
LAMBDA(k,col,
LET(
dept, XMATCH(k-1,accRows,-1),
k₁, k - XLOOKUP(k-1,accRows,accRows,,-1),
INDEX(Recordλ(dept,k₁),col)
)
)
)
)
Again, as an aside, it is not always necessary to append the tables. Sometimes calculations such as lookups or filters can be carried out on a 'by table' basis, and the results aggregated rather than the individual tables.