200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
G'day All; At Teach Excel Forum. I'm working on a small spread sheet with the idea is to collect the Monthly Figures data entries and have collated in there Month as per Fiscal calendar order. I used two different formula, Index/Match and Vlookup. Unfortunately, in this instance both are returning an #N/A error. It would appreciate and be grateful if I can get some assistance.
Many Thanks
Joe
Joe
Unfortunately your MATCH formula in F2 (say) refers to A2 (ID Seq) which isn't part of the range you address so you won't find a match. Personally I'd go simpler...
Another way to do what I think you want is to use a simple IF formula in F2 (then fill right only) as follows:
=IF(AND(MONTH($B2)=MONTH(F$1),YEAR($B2)=YEAR(F$1)),$E2,0)(where the $ fixes row or column to suit) which will return the value from column E if both the month and year in B match that in the header F. Otherwise it produces a 0 (which is suppressed by un-checking the Advanced option "Show a zero in cells that have zero value" under subsection Display options for this worksheet).
Then the formula in F3 (to fill right and down) just adds that previous value to the same IF statement, so:
=F2+IF(AND(MONTH($B3)=MONTH(F$1),YEAR($B3)=YEAR(F$1)),$E3,0)See attached revised file.
This seems to work well and, if an entry is made out of date sequence (say at the start of the following month rather than the same month), the totals for that month update to suit.
Another option would be to collate values in a Pivot Table (on another sheet)
Note that in cell B3 (in yellow), I've set the Custom format to ddd dd/mmm/yyyy so you see Fri 09/Jul/2021 although the background date is still 09/07/2021 If you use that, you might be able to eliminate columns C and D but still enter dates in the form 01/01/2022.
Hope this helps.