Return last column index greater than zero

**denise001** · 09-20-2010, 07:06 AM

Hello Forum

Hope this is a quick fix - I have been struggling with the worksheet functions INDEX/OFFSET to get the answer I need but cannot get the formula right. At least I think it is a combination of more than one function?

Basically I want to return the last development year for a rows of data that I have.

So in the first example in cell A3 I want to see 13 as the answer - not 17 (I have to leave the all the zeros in).

In cell A4 I want to see 8 as the answer - again, not 17.

Many thanks Forum.
denise

**NBVC** · 09-20-2010, 07:08 AM

Try:

=LOOKUP(2,1/($B3:$R3>0),$B$2:$R$2)

copied down.

**denise001** · 09-20-2010, 07:13 AM

Thanks you so much NBVC.
That is perfect - problem solved - and I have learnt something - I was getting very close as well.
(I will be making a donation via paypal to one of your listed charities)
best regards

**NBVC** · 09-20-2010, 07:21 AM

That's great! Thanks.

**roamin_oz** · 07-16-2011, 10:54 PM

NBVC.... how about if I needed to find the opposite.... so the first column that is greater than 0.

So using densie001 work sheet the answers would be.....

a3 = 2
a4 = 2
a5 = 2
a6 = 1
a7 = 1

Cheers Mark