Excel 2007 : Find last non- zero item in a row of values

**tlafferty** · 04-17-2012, 03:12 PM

I have a random assortment of static values ranging from 0-5 in cells C3:Y3. How do I locate the highest column number containing a non zero? I've tried:

Please Login or Register  to view this content.

Which of course will locate the first 0 and return the position in the array. Note that zeros may appear in the row more than once, followed by non zero values:
0,1,5,0,0,5,2,3,0,0,0,0,5,3,4,3,2,0,0,1,0,0,0

In the above sample, the 20th item in the array is a 1 which is what I'd like to return the position for.

**tom1977** · 04-17-2012, 03:20 PM

Hi look into attachment

**tigeravatar** · 04-17-2012, 03:23 PM

Non-array formula solution:
=LOOKUP(2,1/(C3:Y3<>0),ROW(INDIRECT("1:"&COUNTA(C3:Y3))))

**tlafferty** · 04-17-2012, 03:25 PM

Tom1977, that was perfect! Tigeravatar, when I get a chance I'll check your solution out also...Thanks!

**daddylonglegs** · 04-17-2012, 03:53 PM

This should also work, it finds the relative position in the range

=MATCH(2,INDEX(1/(C3:Y3<>0),0))

**tshachaf** · 05-02-2014, 01:27 PM

Originally Posted by daddylonglegs

This should also work, it finds the relative position in the range

=MATCH(2,INDEX(1/(C3:Y3<>0),0))

That is Genius
Could you please explain how that works.

**Person Over Here** · 07-07-2014, 06:43 PM

daddylonglegs, I am also interested in understanding how this works. I have used MATCH and INDEX, but I do not how the portion after INDEX; specifically, I am not familiar with dividing 1 by a range of non-zero numbers. I have not been able to find an explanation online. Would you mind pointing me to where I could find an explanation or sharing how this works?

Thank you.