PDA

View Full Version : Solved: Using Index and Match with Dates



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.

Opv
01-25-2013, 08:13 PM
I figured out my problem. My issue had nothing to do with the formula. I wasn't accounting for some hidden columns.