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

4
votes

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?

3
excelexcel-formula

3 Answers

8
votes

You are very close:

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

Enter

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

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

-2
votes

For me, the best solution for numeric values was this:

=IF(D497<>0,D497,IF(D496<>0,D496,IF(D495<>0,D495,IF(D494<>0,D494,IF(D493<>0,D493,D492)))))

it gives the last non zero value, for your require range that you can make by adding further rows to the formula.