Forum Discussion
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...
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
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)
