End of column

[JC]

I have a budget spreadsheet with a worksheet dealing with loans
between different accounts. Columns A to D contain the details
for one loan and columns E to H the details for a second loan.

Entries are made against each loan as they are added to or
payment is made with the result that they are now ending on
different rows. This could be D56 for one loan and H12 for the
second loan.

To make it easy to see where each loan is I have put a row at the
top of the sheet above the Freeze pane setting nominating the
amount owed.

What I need is a formula to automatically pick up the amount in
the last cell used in each of the columns D and H and write the
amounts in those cells in D2 and H2 respectively.

Any ideas on how this can be done?

 

[T. Valko]

Try these:

=LOOKUP(1E100,D3100)

=LOOKUP(1E100,H3:H100)

 

[JC]

Hi,

That worked like a charm. However, I am puzzled why it works.

The help says:-

LOOKUP(lookup_value,array)

Lookup_value A value that LOOKUP searches for in an array.
Lookup_value can be a number, text, a logical value, or a name
or reference that refers to a value.

If LOOKUP can't find the lookup_value, it uses the largest value
in the array that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first row
or column (depending on the array dimensions), LOOKUP
returns the #N/A error value.

I had looked at this function but figured that it would always
find the biggest value in the column that was less than the
lookup_value used which would not necessarily be the last value
in the column.

Why does it select the last value?

JC

 

[Roger Govier]

Hi
If you had an ascending series of numbers, then the above would be true

If the series is not ascending, Lookup is not trying to find the largest
(i.e Maximum value). It does not hold each value in turn and carry out a
comparison to see whether this is higher than the previous highest, it just
keeps looking down the list for the value chosen.
With that value being a very large number, and not likely to occur, Lookup
will return what is the last value it found in the list.

If dealing with text, then one looks for text that is unlikely to occur in
the column. That could be an unusual character, or say a string of Z's
=LOOKUP(REPT("Z",26),D3100)

 

[JC]

I've left a suggestion to Microsoft that they update their
description to say how the function REALLY works.

JC