4
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.
davidrsinger
Posted 2014-09-26T12:34:10.077
Reputation: 41
3
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)
Raystafarian
Posted 2014-09-26T12:34:10.077
Reputation: 20 384
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)
duDE
Posted 2014-09-26T12:34:10.077
Reputation: 14 097
This works, but it's a faster calculation to no use an array formula. – Raystafarian – 2014-09-26T12:47:10.823
This is not invariably the case :) Take a look: http://www.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.
– duDE – 2014-09-26T12:50:28.457True, 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.
A good point :) – duDE – 2014-09-26T13:02:27.450
1Probably no difference between the two in this particular application, assuming the amount of data isn't unreasonable. – Raystafarian – 2014-09-26T13:03:12.420
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)
Rajesh S
Posted 2014-09-26T12:34:10.077
Reputation: 6 800
The
LOOKUP(2,1/...approach slows down my spreadsheets a lot for some reason. – Museful – 2015-05-03T21:57:18.907