Forum Discussion
Solved
Formula to find the Non blank value between the ranges
Dear Experts, I am struggling with an excel formula to find the exact non blank value for certain criteria. Kindly review the attachment. Yellow cells are the inputs which will change dynamically....
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)