Table lookup formula

[agent154]

I'm starting to go mad, because I can't think of how to do this.

I have a bank account ledger in a table, with column A holding dates, column B holding descriptions, and column K holding balance information I want to look up.

I wish to look in the table for the first row matching an item in column B, but only if it falls between two dates in colunm A. So far I've found how to return the first row that matches in the whole table, but that isn't good enough. The formula I have is as follows:

=OFFSET(INDEX(Account[Balance],MATCH("Pay",Account[Payer or Payee],0)),-1,0)

This looks in my table for "Pay" in the "Payer or Payee" column (which is column b in this case), and returns the value in column K, of the row just above it.

How can I modify this so that it will only consider a chunk of the table, instead of the whole thing? http://www.excelforum.com/editpost.php?do=editpost&p=2603606

 

[agent154]

I've tried using the MATCH function to return the first row after TODAY(), but it is returning erroneous information.

=MATCH(TODAY(),Account[Date],0) returns 213, when today's date is 9/20/2011, and 213 contains yesterday's date. I even try to replace the 0 with -1 to find the next row after today's date, and it returns #N/A.