How to lookup the last value in a row (non zero)?

Posted by Denis on January 22, 2001 7:39 AM

I need to select the last cell in a row of values. This cell must be the last one which is not zero.
Eternal gratefulness assured.
Denis

Posted by Aladin Akyurek on January 22, 2001 12:28 PM

I'll assume the values occupy the range A1:G1.

Array-enter the following formula (that is, hit control+shift+enter to enter it)

B1 =INDEX(A1:G1,MAX(IF(ISNA(MATCH(IF(A1:G1&LT;&GT;0,A1:G1,""),A1:G1,0)),"",MATCH(IF(A1:G1&LT;&GT;0,A1:G1,""),A1:G1,0))))

Hope this works.

Aladin

Posted by Aladin Akyurek on January 22, 2001 1:33 PM

: Eternal gratefulness assured. : Denis

If you don't want to return a text value, use the following array-formula

B1 =INDEX(A1:G1,MAX(IF(ISNA(MATCH(IF(A1:G1&LT;&GT;0,IF(ISNUMBER(A1:G1),A1:G1,"")),A1:G1,0)),"",MATCH(IF(A1:G1&LT;&GT;0,IF(ISNUMBER(A1:G1),A1:G1,"")),A1:G1,0))))

The above formula gives you, if available, a numeric non-zero value.

Aladin

Posted by Jeff Hendricks on January 25, 2001 11:25 AM

I am a little bit confused by the above conversation. Why was the cell B1 used if it lies within the range of values that are being examined (A1:G1)? Does that mean that the formulas should be entered in that cell, or is B1 part of the formula? My question stems from the fact that I tried to use this formula to give B2 the value of the last non-zero value within the range of A1:G1 so I typed in the formula in B2, and it gave me the value in A2, or some apparently random values. If you know what I am doing wrong I would greatly appreciate any help. Thanks a million.

Posted by Aladin Akyurek on January 27, 2001 5:16 AM

Hi Denis,

Forget all the previous answers, see instead:

9082.html

Aladin