FIND THE LAST CELL ENTRY IN A ROW

[Carolyn Bennett]

I am creating a document where I want excel to seach along a row and find the
last entry (the cell with information on the far right of the row). This
information will change constantly, and I will need excel to keep check for
new entries to the right of it.

 

[Biff]

Hi!

Take your pick:

If the data is numeric:

=LOOKUP(MAX(A1:Z1)+1,A1:Z1)

If the data is text:

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

If the data is mixed:

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

Biff

 

[Carolyn Bennett]

Biff,

That was exactly what I wanted, thank you so much! I have no idea how it
worked, but it worked.
I don't suppose you would now know how to now that I have worked out what
the last entry in the in the row is, how I can tell it to reference the cell
two cells vertically above it?

 

[Biff]

It all depends!

It could be as easy as:

=OFFSET(B4,-2,MATCH(A1,B4:IV4,0)-1)

Where:

A1 = result of your lookup formula
Row 4 is the row that the lookup formula was used on
B4 is the first cell in row 4 that begins the data range

More detail would be helpful!

Biff

 

[Harlan Grove]

Take your pick:

If the data is numeric:

=LOOKUP(MAX(A1:Z1)+1,A1:Z1)

....

Why go through the range twice? Also, if there were any errors in the range,
the MAX call and thus the LOOKUP call would propagate the first error
encountered. Use .999999999999999E308 rather than the MAX call to be sure to
return the last numeric value if there are any.

If the data is mixed:

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

Begging the question what to do if the last nonblank (strict sense) cell
evaluates to "". An alternative,

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),A1:Z1)

Actually, light testing shows that the following work.

=LOOKUP(TRUE,ISNUMBER(A1:Z1),A1:Z1)

=LOOKUP(TRUE,ISTEXT(A1:Z1),A1:Z1)

=LOOKUP(TRUE,NOT(ISBLANK(A1:Z1)),A1:Z1)

 

[Aladin Akyurek]

Lets suppose that you want the last numeric entry from row 3...

=LOOKUP(9.99999999999999E+307,3:3)

would yield that entry if row 3 is not empty.

If you replace LOOKUP with MATCH in the above formula, you will get the
position of the last numeric entry.

If you want the value from row 1 which is associated with the last
numeric entry in row 3...

=LOOKUP(9.99999999999999E+307,3:3,1:1)

Put up in terms of exact ranges, e.e.,:

=LOOKUP(9.99999999999999E+307,C3:Z3)

=MATCH(9.99999999999999E+307,C3:Z3)

=LOOKUP(9.99999999999999E+307,C3:Z3,C1:Z1)

Carolyn Bennett wrote:
[...]

I don't suppose you would now know how to now that I have worked out what
the last entry in the in the row is, how I can tell it to reference the cell
two cells vertically above it?

[...]

 

[Biff]

====================

=LOOKUP(MAX(A1:Z1)+1,A1:Z1)

Why go through the range twice? Also, if there were any errors in the range,
the MAX call and thus the LOOKUP call would propagate the first error
encountered. Use .999999999999999E308 rather than the MAX call to be sure to
return the last numeric value if there are any.
====================

It's just a personal preference. It's easier than trying to count the number
of 9s when typing .999999999999999E308 or 9.99999999999999E+307. There's no
doubt it's more efficient but I also believe that the vast majority of
spreadsheets are not so robust that efficiency is a primary concern. At the
most, that formula only goes through a full row. That is a good point about
the possibility of errors, though.

Biff

 

[Biff]

I'm curious as to how you and Aladin would handle this scenario:

You have a spreadsheet that you keep for your bowling league. The bowlers
scores are recorded and one of the formulas you use is to lookup the last
score posted in row 2. Assume the cells for the scores have data validation
applied so that only TRUE bowling scores can be entered.

Which formula would you use (if either) and why?

=LOOKUP(9.99999999999999E+307,2:2)

=LOOKUP(301,2:2)

I would use the 2nd.

Biff