Opv
01-25-2013, 06:42 PM
The following formula uses both Index and Match in an attempt to match the date for which a financial report is due to the array of transaction dates and matches the highest transaction date for the prescribed report month.
=OFFSET(INDEX(TDate,MATCH(ReportMonth,TDate,1),1),0,7)
It worked flawlessly prior to changing what use to be a 1 (the transaction date range) to a 7. The data in column 7 is derived from a formula which calculates the current balance. Consequently, my objective is for the above formula to match the report month date with the array of transactios dates and then return the balance as of that date. For some reason, it's returning a 0, even though there is an outstanding balance.
I've tested the formula by changing the 7 to an 8 (a text value) and it returns the text value with no problem. Can someone advise why the formula would not return the results of a formula in column 7?
======
Followup -- I initially posted the incorrect formula. Have pasted in the correct formula.
=OFFSET(INDEX(TDate,MATCH(ReportMonth,TDate,1),1),0,7)
It worked flawlessly prior to changing what use to be a 1 (the transaction date range) to a 7. The data in column 7 is derived from a formula which calculates the current balance. Consequently, my objective is for the above formula to match the report month date with the array of transactios dates and then return the balance as of that date. For some reason, it's returning a 0, even though there is an outstanding balance.
I've tested the formula by changing the 7 to an 8 (a text value) and it returns the text value with no problem. Can someone advise why the formula would not return the results of a formula in column 7?
======
Followup -- I initially posted the incorrect formula. Have pasted in the correct formula.