INDEX/MATCH to find the last cell in a row range that is not zero?

[excelos]

Hello

I have this row 1:
0,0,0,0,2,0,0,0,0,3,0,0

I want to put a formula in the row below (row 2) that will return the first non zero value searching from right to left in the range from $A$1 to [current column, row1]

Any idea?

Thanks!

 

[Peter_SSs]

What Excel version are you using? That will determine what functions are available to use.
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

I'm not sure why you are trying to specify what functions to use. Wouldn't any function(s) that get the result do?

What about this (expand beyond column Z if you might have more than 26 columns with numbers)

20 10 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	0	0	0	0	2	0	0	0	0	3	0	0
2	3
Last non-zero

Cell Formulas
Range	Formula
A2	A2	=LOOKUP(9.9E+307,A1:Z1/(A1:Z1<>0))

 

[pgc01]

Hi

Another option:

=LOOKUP(2,1/A1:Z1,A1:Z1)