Find contents of the last cell in a row

[Guest]

I'm trying to create a fn that will give me the contents of the last cell in
specific rows. The column of that cell differs by row, unfortunately, and
occasionally the content is '0', which I would need to have as a result of
the formula. Any assistance would be greatly appreciated. Thanks.

 

[Bob Phillips]

=INDEX(A1:A10,MAX(IF(A1:A10<>"",ROW(A1:A10))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Bob Phillips]

=INDEX(A1:A10,MAX(IF(A1:A10<>"",ROW(A1:A10))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

This is giving me some strange results... Here's an example of what I'm
looking for.

a b c d e
One 4 5 6
Two 4 5 6 7
Three 3 2 1 0
Four 2 1 0

For row "One" I would need "6", Two "7", Three "0", Four "0".

Thanks again.

 

[Guest]

This is giving me some strange results... Here's an example of what I'm
looking for.

a b c d e
One 4 5 6
Two 4 5 6 7
Three 3 2 1 0
Four 2 1 0

For row "One" I would need "6", Two "7", Three "0", Four "0".

Thanks again.

 

[Guest]

Try this

=LOOKUP(9.99999999999999E+307,$A1:$D1)

This is giving me some strange results... Here's an example of what I'm
looking for.

a b c d e
One 4 5 6
Two 4 5 6 7
Three 3 2 1 0
Four 2 1 0

For row "One" I would need "6", Two "7", Three "0", Four "0".

Thanks again.

 

[Guest]

Try this

=LOOKUP(9.99999999999999E+307,$A1:$D1)

This is giving me some strange results... Here's an example of what I'm
looking for.

a b c d e
One 4 5 6
Two 4 5 6 7
Three 3 2 1 0
Four 2 1 0

For row "One" I would need "6", Two "7", Three "0", Four "0".

Thanks again.

 

[Gord Dibben]

I believe Bob may have misread the question.

Try this also as an array function.

=INDEX(A1:M1,MAX(IF(A1:M1<>"",COLUMN(A1:M1))))


Gord Dibben MS Excel MVP

 

[Gord Dibben]

I believe Bob may have misread the question.

Try this also as an array function.

=INDEX(A1:M1,MAX(IF(A1:M1<>"",COLUMN(A1:M1))))


Gord Dibben MS Excel MVP