Find last non-zero cell's column reference in an UNORDERED range of values

Question

I have a range with numbers in a row in Excel, say:

          A  B  C  D  E  F  G  H  I  J  K  L
Line 1 => 0  0  0  3  2  8  3  6  0  0  0  0

All cells are non-blank but some contain zeros and some contain numbers. Also, the range cannot be ordered in either ascending or descending order.

I need a formula (not VBA) that will return the column of the last non-zero value in the range (i.e. column 8 for cell H1 above). I can get the actual value of cell H1 with this formula:

 LOOKUP(2,1/(A1:A10<>0),A1:A10)  

but I cannot find a way to get the column number for that cell.

Any ideas?

Answer 1

You are very close:

=LOOKUP(2,1/(A1:L1<>0),COLUMN(A1:L1))

Answer 2

Enter

=MAX(IF(YourRange=0,0,COLUMN(YourRange)))

as an array formula (CTRL-SHIFT-ENTER).