Lookup, Index or Offset

[Guest]

working with amortization table. i am plugging in various payments greater
than minimum ot amortize. I have a formula, that places the row number in
col g, where the ending balance is <=$0. I then want to use that row number
and go over two rows to pick up the date field.

e.g., if loan in paid off on payment 148 and that date is 8/01/2009, that
date is what I want to place at the top of the form for "Payoff Date..."

can not seem to get the correct date returned?

 

[Guest]

sounds like = Vlookup
since Vlookup typically wants the data ascending not descending you need a
workaround. take the formula you are using that provides row number and
instead have it return "PAID" in column G
=if(e__ <=0,"PAID","") <==copy down all rows

=Vlookup("Paid",G2:I42,3,True) < ====set G2:I42 to your actual (G_:I_)

should return the value from col I that is in same row as "Paid"