Hi there,

I am using this formula to find the last entry with today's date in an array
in a bank statement tracking sheet, which shows the current balance of the
account based on today's date. This works perfectly when today's date is in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance prior to
today's date if no transactions have been recorded for today - e.g. the last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that €œThe
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.