Menu
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?
332
Return the row number of the last non blank cell: Enter the formula: =SUMPRODUCT(MAX((A2:A20<>"")*ROW(A2:A20))) into a blank cell to locate the calculated result, and then press Enter key to return the correct result, see screenshot: Note: In the above formulas, A2:A20 is the range of cells that you want to use.
Locate the last cell that contains data or formatting on a worksheet. To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.
You are very close:
=LOOKUP(2,1/(A1:L1<>0),COLUMN(A1:L1))
Enter
=MAX(IF(YourRange=0,0,COLUMN(YourRange)))
as an array formula (CTRL-SHIFT-ENTER).
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
