Forum Discussion
Let statement display multiple results in separate columns
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.
