Finding the rightmost occupied cell

[Paul Hyett]

If I have a range of cells across a row, where only some of the cells on
the left of the range are occupied, is there a way I could extract the
value in the rightmost of the occupied cells (Excel 2003)?

 

[Bob Umlas]

If your data is in A19:N19, for example:
=LOOKUP(2,1/(A19:N19<>""),A19:N19)
Yes, I know it looks odd, but it works.

Bob Umlas
Excel MVP

 

[T. Valko]

The best formula to use depends on what type of data is in the cells.

If the data is numeric:

=LOOKUP(1E100,A1:J1)

If the data is text:

=LOOKUP(REPT("z",255),A1:J1)

And then there's formulas for *mixed* data and formulas to exclude cells
with text formula blanks and formulas for....

So, it depends!

 

[Bob Umlas]

Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)

The best formula to use depends on what type of data is in the cells.

If the data is numeric:

=LOOKUP(1E100,A1:J1)

If the data is text:

=LOOKUP(REPT("z",255),A1:J1)

And then there's formulas for *mixed* data and formulas to exclude cells
with text formula blanks and formulas for....

So, it depends!

 

[T. Valko]

=LOOKUP(REPT("z",255),A1:J1)

That's the pedantic technique.

If it was my file and I knew the data and application I'd probably use:

=LOOKUP("zz",A1:J1)

--
Biff
Microsoft Excel MVP

Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)

 

[Shane Devenshire]

Hi,

Very cute!

How about

=LOOKUP("ÿ",4:4)

The item in the first argument is the CHAR(255)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)

 

[Paul Hyett]

The best formula to use depends on what type of data is in the cells.

If the data is numeric:

=LOOKUP(1E100,A1:J1)

The data is a value generated by a formula in each cell.

Thank you to everyone who replied - I'll try each solution to find which
works best.

 

[Lori]

Excel uses it's internal sort order for text comparisons - not character code -
so CHAR(255) won't work in general if there are any z's in the range.

REPT("z",255) is inefficient and won't work in general if there are any
other character sets or unicode symbols in the range.

If it's standard text try a symbol that sorts after z, eg any greek
character:
=LOOKUP("α",A1:J1). To be robust use: =LOOKUP("々",A1:J1)

Running this script... for i=1 to 65535: cells(i,1)=chrw(i): next i
from the immediate window and sorting shows the order.
Several characters including the one above (12293) appear to be outside
the normal order and seem to work in all cases.

Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)