LAST VALUE IN A COLUMN

I am looking for a formula that will get the last value text or numeric ignoring blanks.I have seen a few but they are mostly Volatile functions they slow down the process in a large spread sheet it's noticable. Any suggestions?
SvgmassiveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
Format the range as TEXT:

=INDEX(range,MATCH(REPT("z",255),range))

Open in new window


Mixed formatting as TEXT and NUMERIC:

=INDEX(range,MAX(MATCH(9.99999999999999E+307,range),MATCH(REPT("z",255),range)))

Open in new window

0
byundtMechanical EngineerCommented:
To get the last number:
=LOOKUP(10^307,A:A)

To get the last text:
=LOOKUP("zzzzz",A:A)

To get the last number or text:
=INDEX(A:A,MAX(IFERROR(MATCH(10^307,A:A,1),1),MAX(IFERROR(MATCH("zzzzz",A:A,1),1)))

If you need to ignore cells that contain an empty string =""
=INDEX(A:A,MAX(IF(LEN(A:A)>0,ROW(A:A),""),1))            
This formula must be array-entered, and is slower than the others
0
barry houdiniCommented:
This formula will give you the last value in column A - number or text, ignoring blanks or "null strings"

=LOOKUP(2,1/(A:A<>""),A:A)

If there is no value you get #N/A

regards, barry
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

SvgmassiveAuthor Commented:
byundt IFERROR  what's the 2003 alternative for the users that doesnt have 2003.
also what's the difference between 9.99999999999999E+307 and 10^307.thanks
0
byundtMechanical EngineerCommented:
The IFERROR function was added in Excel 2007. The Excel 2003 equivalent formula for either text or number is:
=INDEX(A:A,MAX(IF(ISNA(MATCH(10^307,A:A,1)),1,MATCH(10^307,A:A,1)),IF(ISNA(MATCH("zzzzz",A:A,1)),1,MATCH("zzzzz",A:A,1))))

That said, barryhoudini had a more efficient formula. It won't work with Excel 2003 and earlier because it refers to an entire column, but you could use all but the last row with:
=LOOKUP(2,1/(A$1:A$65535<>""),A$1:A$65535)

The difference between 9.99999999999999E+307 and 10^307 is like the difference between 1 and 9.99999999999999. E+307 is interpreted as 10^307, so the first number is equal to 9.99999999999999 times 10^307.
0
SvgmassiveAuthor Commented:
how do i get the address from =LOOKUP(2,1/(A:A<>""),A:A).Thanks
0
byundtMechanical EngineerCommented:
The LOOKUP function has several features that may not be apparent from looking at the online Help that Microsoft publishes for it:
1. LOOKUP searches only for one type of value. If the first parameter is text, it only searches for text. If the first parameter is a number, it only searches for a number.
2.  LOOKUP ignores any values that aren't of the same type as the first parameter. If the second parameter includes error values, they get ignored.
3.  LOOKUP assumes that the second parameter is sorted in ascending order, but doesn't require that fact. LOOKUP expects to find a series of values that bracket the first parameter. So if the first parameter is 5, it expects the second parameter to be a series of values like 1, 3, 4, 6, 8. In that series it will find the 4, which is the largest value less than the first parameter.
4.  LOOKUP allows an optional third parameter, which is an array of values. When used, it will do the matching with the first and second parameters, then return a corresponding value from the array of values in the third parameter.
5.  If LOOKUP is asked to find a value larger than anything in the second parameter, it returns the last value.
6.  LOOKUP does array-formula type things, but doesn't have to be array-entered (Control + Shift + Enter).

Putting it all together, you don't get an "address" from =LOOKUP(2,1/(A:A<>""),A:A). Instead, you get a value from the third parameter.

In that formula, LOOKUP has three parameters.
=LOOKUP(value to find, values to look in, values to return from)

The first parameter of LOOKUP is the value to find. In this case, the formula is looking for a 2. Because we want the formula to return the last value, the 2 should be larger than any value in the second parameter.

The second parameter of LOOKUP is an array of values to look in. In this case, it is a Boolean expression 1/(A:A<>""). The denominator of this expression (A:A<>"") is an array of TRUE or FALSE values. Because those TRUE or FALSE values are used in an arithmetic operation (1/Boolean value), they are converted to 1 for TRUE and 0 for FALSE. So the entire expression returns an array of either 1 or #DIV/0! error values. As previously mentioned, the error values will be ignored.

The third parameter is the array to return values from. As written the formula is returning a value from column A. In particular, it is returning the last value in column A, regardless of type. And it also ignores empty strings ="".

Getting back to your question about address, if you want values from columns A, B and C on the same row as the last value in column A, you would write the formula as:
=LOOKUP(2, 1/($A:$A<>""), A:A)
When copied across, this latter formula would return first return the last value from column A, then the corresponding values on that same row from columns B and C.

There is no need for LOOKUP to return an address because it can return the desired value(s) directly.
0
SvgmassiveAuthor Commented:
Nice explanation byundt. I would like to use it in a name range also to get the address of the cell with the last value.
0
barry houdiniCommented:
Will the named range be a single column? Could it be the whole column?

regards, barry
0
SvgmassiveAuthor Commented:
single column
0
barry houdiniCommented:
You can use MATCH in a similar way to LOOKUP to get the row number within the range, e.g. assuming your named range starts at row 1 (and isn't the whole column) this "array formula" will give you the row number of the last value

=MATCH(2,1/(Named_range<>""))

confirmed with CTRL+SHIFT+ENTER

or if you want the actual cell address like $Z$6 then try this version

=CELL("address",INDEX(Named_range,MATCH(2,1/(Named_range<>""))))

also confirmed with CTRL+SHIFT+ENTER

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.