Last Word or Number in column

mgirvin

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,251
Office Version
  1. 365
Platform
  1. Windows
Dear Most Amazing Excelers In The World,

I have used this formula to get the last value in a column:

=LOOKUP(9.99999999999999E+307,Items!A:A)

That seems to be the most elegant formula that I have seen at this Message Board.

But what about the most elegant formula to get the last word or value or error or TRUE/FALSE in a column?

I have used this formula (entering it with Ctrl + Shift + Enter):

{=OFFSET(Items!A1,MAX(ROW(Items!A:A)*(Items!A:A<>""))-1,0)}

How many of you know other more elegant or efficient formulas?
 
Sort by date Sort by votes
Switch from number to text

=LOOKUP(REPT("z",255),Items!A:A,ROW(A:A))

For boolean

=LOOKUP(TRUE,Items!A:A,ROW(A:A))

You could MAX the three results I guess... no doubt there is a more elegant way.
 
Upvote 0
Cheers VoG (what happened to II, is that recent ?)

I figured the LOOKUP method isn't good for finding the last cell of data given you may not have all types listed and would thus need to double evaluate which isn't very clever.

Time to lay off the plonk and get offline before I post something even more hideous.
 
Upvote 0
DonkeyOte said:
Cheers VoG (what happened to II, is that recent ?)
Click to expand...

Hi Luke

I dropped the II about a month ago thanks to Richard. There was only one (and will only be one) VoG so I reverted to the same moniker that I use on other boards.

Have a great 2009!

Regards, Peter
 
Upvote 0
mgirvin said:
Dear Most Amazing Excelers In The World,

I have used this formula to get the last value in a column:

=LOOKUP(9.99999999999999E+307,Items!A:A)

That seems to be the most elegant formula that I have seen at this Message Board.

But what about the most elegant formula to get the last word or value or error or TRUE/FALSE in a column?

I have used this formula (entering it with Ctrl + Shift + Enter):

{=OFFSET(Items!A1,MAX(ROW(Items!A:A)*(Items!A:A<>""))-1,0)}

How many of you know other more elegant or efficient formulas?
Click to expand...

For the last text value, including a formula blank (i.e., "")...

=LOOKUP(REPT("z",255),Items!A:A)

For the last cell's value (any value)...

=LOOKUP(2,1/ISBLANK(Items!A1:A400),Items!A1:A400)

Note the use of a definite range reference instead of a whole column reference (a restriction that is obsolete in Excel 2007).

The latter can be made to apply to a whole column reference for the prior versions as follows...

=IF(ISBLANK(Items!A65536),LOOKUP(2,1/ISBLANK(Items!A1:A65535),Items!A65536)

For the last logical value...

=IF(ISLOGICAL(Items!A65536),LOOKUP(2,1/ISLOGICAL(Items!A1:A65535),Items!A65536)

The same method can be applied for errors or for specific error types, etc.

Note. The 2 bit in the last LOOKUP formulas can be replace with Excel's limit value of 9.99999999999999E+307.

How all these work can be found at the following links:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/board2/viewto...=502525#502525

http://www.mrexcel.com/forum/showthread.php?t=310278
 
Upvote 0
Dear Amazing Excelers,

This works great for text: =LOOKUP(REPT("z",255),Items!A:A,ROW(A:A))

As for the any value formula, from Aladin’s formulas I got these to work:

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

=IF(Items!A65536<>"",Items!A65536,LOOKUP(2,1/(Items!A1:A1:Items!A65535<>""),Items!A1:A1:Items!A65535))

I could not get this formula to work (I got 0 (zero) as my answer):

=LOOKUP(2,1/ISBLANK(Items!A1:A400),Items!A1:A400)

When I changed it to this:

=LOOKUP(2,1/NOT(ISBLANK(Items!A1:A400)),Items!A1:A400)

I got it to work.

Did I do something wrong with this formula: =LOOKUP(2,1/ISBLANK(Items!A1:A400),Items!A1:A400) ??

Finally,

What are the advantages and disadvantages between these two:

=IF(Items!A65536<>"",Items!A65536,LOOKUP(2,1/(Items!A1:A1:Items!A65535<>""),Items!A1:A1:Items!A65535))

{=OFFSET(Items!A1,MAX(ROW(Items!A:A)*(Items!A:A<>""))-1,0)}

Is one more efficient or elegant than the other?
 
Upvote 0
mgirvin said:
Dear Amazing Excelers,

This works great for text: =LOOKUP(REPT("z",255),Items!A:A,ROW(A:A))
Click to expand...

If you want the position, MATCH will do:

=MATCH(REPT("z",255),Items!A:A)

This formula and the one for the last numeric value are fast. We do not need to slow them (right Luke?) with calls like ROW(A:A).

As for the any value formula, from Aladin’s formulas I got these to work:

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

This will get any value as last value except a formula blank (i.e., "") on Excel 2007...

=IF(Items!A65536<>"",Items!A65536,LOOKUP(2,1/(Items!A1:A1:Items!A65535<>""),Items!A1:A1:Items!A65535))
Click to expand...

As above, this won't get "".

I could not get this formula to work (I got 0 (zero) as my answer):

=LOOKUP(2,1/ISBLANK(Items!A1:A400),Items!A1:A400)
Click to expand...

It will return a 0 for "" if this is the last value in the range.

When I changed it to this:

=LOOKUP(2,1/NOT(ISBLANK(Items!A1:A400)),Items!A1:A400)

I got it to work.
Click to expand...

Sure for any last value except for "".

Did I do something wrong with this formula: =LOOKUP(2,1/ISBLANK(Items!A1:A400),Items!A1:A400) ??
Click to expand...

No. It was an example how to use LOOKUP(2,1/(...),(...))

Note. The LOOKUP(2,1/(...),(...)) class of formulas are expensive.

Finally,

What are the advantages and disadvantages between these two:

=IF(Items!A65536<>"",Items!A65536,LOOKUP(2,1/(Items!A1:A1:Items!A65535<>""),Items!A1:A1:Items!A65535))

{=OFFSET(Items!A1,MAX(ROW(Items!A:A)*(Items!A:A<>""))-1,0)}

Is one more efficient or elegant than the other?
Click to expand...

No idea. However, the latter cannot be used on versions prior to Excel 2007.

A bit shortened...

=IF(Items!A65536<>"",Items!A65535,Items!A65536,LOOKUP(2,1/(Items!A1:A65535<>""),Items!A1:A65535))

does not require control+shift+enter, although it crunches arrays.
Click to expand...
 
Upvote 0
Aladin Akyurek said:
If you want the position, MATCH will do:

=MATCH(REPT("z",255),Items!A:A)

This formula and the one for the last numeric value are fast. We do not need to slow them (right Luke?) with calls like ROW(A:A).
Click to expand...

...tucking into my humble pie...:oops:
 
Upvote 0
Dear Team,

As always, I thank you very much for all the great Excel knowledge. It has been very helpful!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
VBA to copy each sheet of a separate (open) workbook into destination workbook
Replies
5
Views
144
Texas Longhorn
T
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
510
Peter_SSs
Peter_SSs
D
  • Question Question
help matching date and using column related to date with copying row data to new new sheet
Replies
1
Views
125
Sam_D_Ben
Sam_D_Ben
AD4GC
  • Solved
VBA to input data into a second sheet using formulas
2 3
Replies
23
Views
652
AD4GC
AD4GC
Burrgogi
  • Question Question
Need some help with VBA - evaluating a string of numbers and from VLOOKUP routine.
Replies
2
Views
248
StephenCrump
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top