Scan 12 columns from right to left and return the first value that is not zero?

  • Thread starter Thread starter Stacy
  • Start date Start date
S

Stacy

I'm trying to scan 12 columns (H to S) from right to left
and neet to return the frist value that is not zero.

I've tried embeded if statements and can get through 9 of
the columns but can not add the 3 additional another
embedded if.

=IF(P8=0,IF(O8=0,IF(N8=0,IF(M8=0,IF(L8=0,IF(K8=0,IF(J8=0,IF
(I8=0,H8,I8),J8),K8),L8),M8),N8),O8),P8)

I have to believe there is a better way but I can't figure
it out.

Any help would be appreciated.

Stacy
 
=INDEX($H$8:$S$8,MATCH(A1,$H$8:$S$8,1)+1)
In A1 you can put any number and place the formula in B1
B1 will then return the first bigger number in your range.

HTH
 
I'm trying to scan 12 columns (H to S) from right to left
and neet to return the frist value that is not zero.
Click to expand...

=LOOKUP(9.99999999999999E+307,IF(H2:S2<>0,H2:S2))

This is an array formula, so hold down [Ctrl] and [Shift] keys before pressing
[Enter].
 
Don't quite understand how it works but it does!

Many thanks for taking the time.

Stacy
-----Original Message-----
I'm trying to scan 12 columns (H to S) from right to left
and neet to return the frist value that is not zero.
Click to expand...

=LOOKUP(9.99999999999999E+307,IF(H2:S2<>0,H2:S2))

This is an array formula, so hold down [Ctrl] and [Shift] keys before pressing
[Enter].
Click to expand...
 
Back
Top