# Finding the last two non-zero value in a row

#### ryan0521

##### Board Regular
Good day,

This is the result I want, please see below.

 last non zero 2nd non zero 3 2 1 3 0 3 1 0 0 2 1 0 1 2 1 0 1 0 0 1 1 2 2 0 1 0 1 2

<tbody>
</tbody>

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

##### MrExcel MVP
In F2 just enter and copy dow:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:E2),A2:E2)

In G2 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:INDEX(A2:E2,MATCH(9.99999999999999E+307,1/ISNUMBER(1/A2:E2))-1)),A2:E2)

If you define BigNum in Name Manager as referring to:

=9.99999999999999E+307

we can have:

=LOOKUP(BigNum,1/ISNUMBER(1/A2:E2),A2:E2)

=LOOKUP(BigNum,1/ISNUMBER(1/A2:INDEX(A2:E2,MATCH(BigNum,1/ISNUMBER(1/A2:E2))-1)),A2:E2)

If so desired, wrap the formulas into an IFERROR call in order to avoid #N/A's when no non-zero values are available.

#### ryan0521

##### Board Regular

Can you please explain it to me why this formula works, so that I can explain it thoroughly to others

##### MrExcel MVP

Can you please explain it to me why this formula works, so that I can explain it thoroughly to others

A. LOOKUP and kindred functions ignore error values in the references they are fed with if they possibly can. (A reference consisting solely of error values will cause such a function to return an error.)

B. LOOKUP and kindred functions with match-type set to 1 recruit most likelily some form of binary search (BS) algorithm, which is very fast.

C. When given a look up value, which is

MAX(reference)+Delta

where Delta is a very small number, in order to locate that value in the reference, LOOKUP hits the last numeric value in that reference, as a side of BS. Thus:

LOOKUP(MAX(A2:A4)+Delta,A2:A4)

will deliver

4

when A2:A4 houses say 6, 0.2, 4.

D. Since MAX is slow and returns error whenever an error value occurs in the reference it looks at, we choose a value improble to occur in the references of interest to us:

9.99999999999999E+307

So we get a construct like this:

LOOKUP(9.99999999999999E+307,Reference)

This big number is a constant of Excel itself, therefore a reliable, recognizable standard. See for more: http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

One of the formulas we are settled for is:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:E2),A2:E2)

>> (re-written)

=LOOKUP(9.99999999999999E+307,X,Y)

correlates the last numeric value of X with the value of Y that is at the same position.

The last numeric value of X must be a non-zero value (the requirement).

ISNUMBER(1/A2:E2) yields TRUE for values different from 0, otherwise FALSE.

1/ISNUMBER(1/A2:E2) yields a number for TRUE's and a #DIV/0! for FALSE's. LOOKUP correlates the position of the last number from X with a number at the same position in Y, which is A2:E2.

http://www.mrexcel.com/forum/hall-f...-construct-return-last-match.html#post3304545
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

Hope this helps.

Last edited:

#### ryan0521

##### Board Regular
Thank you so much.

Replies
2
Views
182
Replies
1
Views
407
Replies
4
Views
51
Replies
10
Views
506
Replies
4
Views
414

1,195,958
Messages
6,012,547
Members
441,709
Latest member
diurpaneus

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back