Let statement display multiple results in separate columns

Brass Contributor

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
)
10 Replies

@dshawSLDC 

 

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.

First thank you for the Compliment. Second the reason I need to put in one formula is I need both columns to work dynamically as the data changes.

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.

Yes I agree I can do that but I was hoping to have it spill with the other formula. So I can reference the spill data elsewhere if I do the xlookup and drag down in the second column and the original data get larger I will have to continue to drag down every time the data changes.

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

@dshawSLDC 

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)
Thanks That was very helpful. Quick question do you know if you can use the choose statement on array?

@dshawSLDC 

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.

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
)

@dshawSLDC 

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.