Wondering why this formula works in one cell and not another and if there is any alternative-CodePudding

this is the formula in question,

=INDEX(C:C,COUNTA(C:C),1)

in this form it works perfectly and displays the latest result from that row and column.

=INDEX(AA:AA,COUNTA(AA:AA),1)

However, the above does not work and just displays blank. Why does the double column identifier ruin it? is there an alternative I can use for these columns?.

CodePudding user response：

most likely you got there an empty cell in that AA column that will offset the result

try this instead:

=INDEX(INDIRECT("AA"&MAX((AA:AA<>"")*(ROW(A:A)))))