On Sun, 5 Aug 2012 12:51:38 -0400, Stan Brown wrote:

I'm trying to do a lookup, but on data that occur in _descending_
order, and for some reason I can't get it to work.

This is a spreadsheet of loan payments, interest, etc. Payment dates
are in B18:B377, and new balances are in I18:I377. I'm trying to
fond the date where new balance first goes to zero. I can't use
VLOOKUP or LOOKUP because the balances are in descending order, so I
tried MATCH:

=MATCH(0,I$18:I$377,0)

and then I'll use INDEX(B18:B377, the MATCH) to get the date. But
=MATCH() returns #N/A and not the row number as expected. Is the
problem that my column I contains formulas rather than values? Excel
help isn't explicit about this, though all its examples are values.

How can I find the first cell in I18:I377 that contains a zero? (I'm
using Excel 2010, but if possible I'd like a formula that also works
in Excel 2007.)

Thanks!

(P.S. This is in aid of my spreadsheet mentioned in "Here is Mortgage
Calculator with Amortization" -- I'm trying to generalize it, and
also display up top the date on which the loan will be paid off.)

MATCH is returning #N/A because there is no cell in that range that is equal to zero. You may have cells that display a zero, but that will be due to rounding of the actual value.

There are several possible workarounds. I would recommend the following to get the pay-off date:

=LOOKUP(2,1/(I18:I3770),B19:B378)

The LOOKUP will match in column I the last number that is greater than zero. I am assuming that once there is a zero (or less), there will be no other values further down the column that are greater than zero, since you are writing about a Mortgage calculator.

Note that the result vector is purposely offset by one row compared with the lookup vector; because the date you want should be one below the last positive value.