pulling last populated cell in a row 1

[dreadnaught]

I am trying to pull the last populated cell in a row.
I have but an example below. It there any formula that will do this?

1 2 3 4 5 6 7 8 9 10 11 12 number
a 7 4 2 5 9 9
b 1 3 5 6 8 9 12 12
c 2 4 4

Thanks,

Dreadnaught

 

[GlennUK]

You could use an array formula like this :

=INDEX(A2:J2,MAX(IF(A2:J2<>"",COLUMN(A2:J2),0)))

entered using Ctrl-Shift-Enter instead of Enter.

Cheers, Glenn.

 

[dreadnaught]

what if the last number in the row is not always the max?

 

[GlennUK]

The formula gets the last number in the row, not the MAX. The MAX function in the formula is to test for the column number where the last number occurs.

Cheers, Glenn.

 

[dreadnaught]

glennuk

I put in the formula exactly and I am getting a return value of 0. I did make sure I did the ctrl shift enter. Any ideas?

 

[GlennUK]

I guessed the references, so check that they are pointing to the right areas.

Also, are the cells that appear to be blank actually blank?

Cheers, Glenn.

 

[dreadnaught]

Actually no they are not blank. It is a query from a data base and it is bringing back zero in that cell.

 

[GlennUK]

Try this :

an array formula like this :

=INDEX(A2:J2,MAX(IF(A2:J2<>0,COLUMN(A2:J2),0)))

entered using Ctrl-Shift-Enter instead of Enter.

Cheers, Glenn.

 

[KenWright]

Another way to find the last value, albeit contingent on your data being either text or numeric as opposed to a mix:-

=LOOKUP(9.99999999999999E+307,A:A) for last numeric entry in a column

=LOOKUP(9.99999999999999E+307,1:1) for last numeric entry in a row

=LOOKUP(REPT("z",255),A:A) for last text entry in a column

=LOOKUP(REPT("z",255),1:1) for last text entry in a row

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------