Last cell populated only

[petiteshiraz]

In columns B through M I have information that corresponds to data for each month. When January comes, I enter that month’s data, when February comes, I do the same.

In column N, I want to have only the info from the last cell that is filled in. In other words, if there is data in cells B4, C4, and D4, I want cell N4 to be populated with the data from cell D4.
How do I do this?

 

[Firefly2012]

If you have mixed datatypes but want to retain the efficiency of the binary search route, you can hack it with an INDEX/LOOKUP/CHOOSE/MATCH combination:

=INDEX(A:A,LOOKUP(9.9E+307,CHOOSE({1,2,3},MATCH(9.9E+307,A:A),MATCH(REPT("z",255),A:A),MAX(MATCH(9.9E+307,A:A),MATCH(REPT("z",255),A:A)))))

I'm not sure this is of much practical use (not in this particularl scenario anyway) seeing as you probably only want to identify the last value the once, but anecdotally this was substantially faster than the LOOKUP(2,1/(A:A<>"")) formula when I applied it to shed-loads of cells in xl2007.

 

[Rick Rothstein]

If you have mixed datatypes but want to retain the efficiency of the binary search route, you can hack it with an INDEX/LOOKUP/CHOOSE/MATCH combination:

=INDEX(A:A,LOOKUP(9.9E+307,CHOOSE({1,2,3},MATCH(9.9E+307,A:A),MATCH(REPT("z",255),A:A),MAX(MATCH(9.9E+307,A:A),MATCH(REPT("z",255),A:A)))))

I'm not sure this is of much practical use (not in this particularl scenario anyway) seeing as you probably only want to identify the last value the once, but anecdotally this was substantially faster than the LOOKUP(2,1/(A:A<>"")) formula when I applied it to shed-loads of cells in xl2007.

Interesting... and unexpected (at least to me). Thanks for posting it.