Forum Discussion
Formula to find the Non blank value between the ranges
As variant with non-array formulas
First after
=INDEX($E4:$W4,1,AGGREGATE(15,6,1/($E4:$W4<>"")/($E$3:$W$3>=--A$1)*(COLUMN($E$3:$W$3)-COLUMN($E$1)+1),1),1)Immed before
=LOOKUP(--A$1,1/($E4:$W4<>"")*$E$3:$W$3,$E4:$W4)
As variant with non-array formulas
First after
=INDEX($E4:$W4,1,AGGREGATE(15,6,1/($E4:$W4<>"")/($E$3:$W$3>=--A$1)*(COLUMN($E$3:$W$3)-COLUMN($E$1)+1),1),1)Immed before
=LOOKUP(--A$1,1/($E4:$W4<>"")*$E$3:$W$3,$E4:$W4)
Must say that your solution is much more elegant than mine and that I'm still learning new things every time. For instance, I had no idea that "--C1" could make the text " '60 " into a number.
Your formulae give #NA and/or #NUM when you look up values for numbers that have no value "before" or "after" where mine produce -zero-. And I guess that this is no problem, as one can always ignore these in calculations (if necessary) by using AGGREGATE. But I keep forgetting this, when I strive to avoid of any kind of error. But, after testing my own formulae a bit more careful now, I notice that they produce #NA if the number looked up is greater than 77. So, I failed 😞
Lastly, I took the requirement "before" and "after" literally. "Equal to" is neither "before" or "after". Therefore I raised the question towards the end of my original post.
Thank you for the feedback.
If error handling is the case I'd prefer to wrap formulas with IFERROR and return some text which indicates the error. That's better to separate blank values and zeroes.
And you are absolutely right with "equal to" case. If to exclude such cases we may change ($E$3:$W$3>=--A$1) on ($E$3:$W$3>--A$1) for AGGREGATE and add one more criteria
($E$3:$W$3<>--A$1)
for the LOOKUP().
Like in attached.
Hi SergeiBaklan ,
I didn't originate this conversation, so I don't know what's required with required to the "equal to" question. Thats up to jebam3001 to determine, as for the error handling. I merely raised the questions since I struggled with it when I worked on my version of the solution. Happy Holidays!