5

I am tracking rooms booked in a hotel on a daily basis in Excel. Every day I input the number of rooms booked in a column to show a trend of pick up over a period. I'd like to return the value of the last entry in a column.

I would post a picture to illustrate, but apparently you have to be at a certain level.

Improve this question

3 Answers 3

Reset to default
5

Sure use this (for numbers) -

=VLOOKUP(9.99999999999999E+307,B:B,1)

Change B:B to whatever column you're using.

or this (for text) -

=LOOKUP(2,1/(B:B<>""),B:B)
Improve this answer
1
  • The LOOKUP(2,1/... approach slows down my spreadsheets a lot for some reason.
    – Museful
    Commented May 3, 2015 at 21:57
3

Try this (found here):

=INDEX(column, COUNTA(column), 1)

For instance, to return the last value in column A, you'd use the expression

=INDEX(A:A, COUNTA(A:A), 1)

Improve this answer
5
  • This works, but it's a faster calculation to no use an array formula.
    – Raystafarian
    Commented Sep 26, 2014 at 12:47
  • This is not invariably the case :) Take a look: decisionmodels.com/optspeede.htm. VLOOKUP is slightly faster (approx. 5%), simpler and uses less memory than a combination of MATCH and INDEX or OFFSET. However the additional flexibility offered by MATCH and INDEX often allows you to make significant timesaving compared to VLOOKUP.
    – Leo Chapiro
    Commented Sep 26, 2014 at 12:50
  • True, but index() is also volatile (for WS structure changes, not cell changes). source - download the zip and watch the volatility if adding/removing a row or column.
    – Raystafarian
    Commented Sep 26, 2014 at 13:01
  • A good point :)
    – Leo Chapiro
    Commented Sep 26, 2014 at 13:02
  • 1
    Probably no difference between the two in this particular application, assuming the amount of data isn't unreasonable.
    – Raystafarian
    Commented Sep 26, 2014 at 13:03
0

You may use any on these formulas:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

=LOOKUP(2,1/(ISNUMBER(A1:A32)),A1:A32)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .