fvschedule+lookup

OK understand the problem

Re: OK understand the problem

Did you solve the problem?

I'd suggest editing this formula a bit:

=FVSCHEDULE(1;oran!C55:oran!C79)

Yes, you can use a lookup function. Apparently, you want to build the second arg (the range for the schedule, which consists of a series of compound interest rates), using the value in A17. Judging from the use of EOMONTH, A17 seems to contain a date.
My question is: Are you trying to use a subset of interest rates that you already have in oran!C55:oran!C79, depending on the value of A17?

Aladin

Yes i am trying to use a subset of interest rates that i already have in oran!C55:oran!C79, depending on the date of A17. In A17 i have a date in aother worksheet and ý have dates and nekt to it i have rates. But when i write =+FVSCHEDULE(1; (+LOOKUP(EOMONTH(A17; 0); oran!A:A; oran!C:C)):oran!C79) it doesn't work. Because lookup returns a value. But formula wants the cell reference example c54. When i write lookup formula it takes the value in the cell.

Gokhan,

You want to get C54, but this cell is outside the range of C55:C79 which contains your rates!

Lets start over.

A17 is a cell where you enter a date.
You don't want to use this directly: you want to use EOMONTH(A17,0) instead as a lookup value to obtain a subrange of C55:C79 from "oran".
If you have dates say in B55:B79 on "oran", then it is not a big deal to build the subrange of the interest rates. Do you have such dates to which the lookup value EOMONTH(A17,0) can be matched?

Aladin

PS. If you want me to look at your worksheet, you know my e-mail.

FVSCHEDULE: Determining the range of rates to use

The formula should be:

=FVSCHEDULE(1, INDIRECT(ADDRESS(MATCH(DATE(YEAR(A5), MONTH(A5)+1,0), oran!A:A,0), 4) & ":" & ADDRESS(SonTefeSirasi,4)))

Given that you have date-indexed schedule of rates in D in sheet "oran", the 2nd arg of this formula computes the range to use conditioned on the value of A5 that contains a date. Since D values are indexed by end-of-month dates, DATE in this formula converts the date in A5 to an end-of-month date and matches the result to the index dates in A in "oran", such that it can compute where the subrange in D should start. SonTefeSirasi (i.e., last row in English) is a named formula that keep tracks the last row in use in D in "oran".

Aladin

Make that

=FVSCHEDULE(1, INDIRECT("oran!" & ADDRESS(MATCH(DATE(YEAR(A5), MONTH(A5) + 1, 0), oran!A:A,0), 4) & ":" & ADDRESS(SonTefeSirasi,4)))

===============