Go to right thru a range and skip all blank cells, return value of first non-blank in rang

This should find the first non-blank value in your range:
=INDEX(AA$1:GA$1,MATCH(TRUE,INDEX((AA1:GA1<>0),0),0))

Edit: this isn't an array formula - just enter as normal

Hi,

i used row 1 columns "A" to "C" in my example, but you'll get the point.


=IF(A1<>"",A1,IF(B1<>"",B1,IF(C1<>"",C1,"All blank")))


hope that helps

Originally Posted by Aardigspook

This should find the first non-blank value in your range:
=INDEX(AA$1:GA$1,MATCH(TRUE,INDEX((AA1:GA1<>0),0),0))

Edit: this isn't an array formula - just enter as normal

Thanks, this is the one that works best for me. However, in an attempt to make this a bit more refined I would like to add a condition to it via an if/and/or combination:

=IF(OR(F952="Liability",F952="Motor",AND(Y952=0)),INDEX(BJ952:GG952,MATCH(TRUE,INDEX((BJ952:GG962<>0),0),0)),Y952)

however, any time "Liability" or "motor" is indeed found the formula spits out an "#N/A" error. Other than that it works fine. I am certain it is a problem with my login in the IF argument.

In summary:

If F1 = "Motor" or "Liability" AND Y1 = 0 THEN go to right thru range from BJ1:GG1 and return the value in the first non-blank cell.

Thanks very much!

=IF(AND(Y1=0, OR(F1="Motor", F1= "Liability")), INDEX(BJ1:GG1,MATCH(TRUE,INDEX((BJ1:GG1<>0),0),0)),Y1)

The OR is inside the AND