p45cal
Well-Known Member
In exploring a formula-only solution to this this thread: https://chandoo.org/forum/threads/vlookup.50070/ I've come across an unexpected block.
To break the problem down into smaller steps I've started with the likes of
38067-38098
in a single cell (a string, not a calculation) and I was aiming to get 38067,38068,38069,38070,38071,38072 etc. in a column of cells.
So in the attached I put that string in cell A1. I've got the result I want in cell Q2 and spill range, but it uses helper cells.
In the attached, the problem formula is in cell D1; I was expecting output as in C1 but I only get a single number. What the f am I (or Excel) doing wrong?!
SEQUENCE isn't working. =LET(d,31,SEQUENCE(d)) works fine, why not in cell D1?
To debug I've put the same LET formula in cell K2:N2 with different final arguments to make sure I'm getting the expected results.
Of course, I might be going around the houses with this formula and there'll likely be more elegant formulae and would like to see them, but I'd still be interested to know why I'm not getting the expected result. I really don't expect to get a decent solution for Ram Chandran's thread unless I look to making lambda formulae, it might even encourage me to look at recursive lambdas, but I doubt I'll get the time. It's become more academic for me to know what I've done wrong.
If I mention @Peter Bartholomew perhaps he'll jump in with some comments?
To break the problem down into smaller steps I've started with the likes of
38067-38098
in a single cell (a string, not a calculation) and I was aiming to get 38067,38068,38069,38070,38071,38072 etc. in a column of cells.
So in the attached I put that string in cell A1. I've got the result I want in cell Q2 and spill range, but it uses helper cells.
In the attached, the problem formula is in cell D1; I was expecting output as in C1 but I only get a single number. What the f am I (or Excel) doing wrong?!
SEQUENCE isn't working. =LET(d,31,SEQUENCE(d)) works fine, why not in cell D1?
To debug I've put the same LET formula in cell K2:N2 with different final arguments to make sure I'm getting the expected results.
Of course, I might be going around the houses with this formula and there'll likely be more elegant formulae and would like to see them, but I'd still be interested to know why I'm not getting the expected result. I really don't expect to get a decent solution for Ram Chandran's thread unless I look to making lambda formulae, it might even encourage me to look at recursive lambdas, but I doubt I'll get the time. It's become more academic for me to know what I've done wrong.
If I mention @Peter Bartholomew perhaps he'll jump in with some comments?