Forum Discussion
dshawSLDC
Feb 26, 2022Brass Contributor
Let statement display multiple results in separate columns
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 for...
mathetes
Feb 26, 2022Silver Contributor
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.
dshawSLDC
Feb 28, 2022Brass Contributor
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.
- mathetesFeb 28, 2022Silver Contributor
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.
- dshawSLDCFeb 28, 2022Brass ContributorYes 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.
- dshawSLDCFeb 28, 2022Brass Contributor
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