Get the value of a cell from the bottom row in a column

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,801

Office Version

1. 365

Platform

1. Windows

• Jan 7, 2009

• #2

To return the last numerical value in a range, try...

=LOOKUP(9.99999999999999E+307,Range)

Hope this helps!

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Jan 7, 2009

• #3

Hello and welcome to MrExcel.

Try

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

 

Upvote 0

R

rickscolaro

New Member

Joined

Jan 7, 2009

Messages

8

• Jan 8, 2009

• #4

Is that good if I want the value returned to a different sheet in the same workbook?

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Jan 8, 2009

• #5

You just need to include the sheet name

=INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A))

 

Upvote 0

R

rickscolaro

New Member

Joined

Jan 7, 2009

Messages

8

• Jan 8, 2009

• #6

Hey thanks. That worked great. I did use the =Lookup function first because that was the first on the list.. I am sure both work well though.. Is there an explanation, (if only for my own satisfaction) that that particular expression does that?

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Jan 8, 2009

• #7

You are welcome.

For an explanation see http://www.xldynamic.com/source/xld.LastValue.html

 

Upvote 0

R

rickscolaro

New Member

Joined

Jan 7, 2009

Messages

8

• Jan 8, 2009

• #8

I just tried the =Index() function and it worked equally well. In both formulas though, I substituted a range name for the actual cell address ranges and it seemed to work just great, I didn't have to put the sheet name in there after all. Thanks to both of you for you help.

rick scolaro

 

Upvote 0