Index Match Match problem

Hi
for income part e5 cell
=IFERROR(INDEX(INPUT!$F$5:$F$35,MATCH(1,(E$3=MONTH(INPUT!$B$5:$B$35))*(INPUT!$G$5:$G$35='2013'!$B5)*(VALUE('2013'!$B$2)=YEAR(INPUT!$B$5:$B$35)),0)),0) CSE

for expenses e10 cell
=IFERROR(INDEX(INPUT!$E$5:$E$35,MATCH(1,(E$3=MONTH(INPUT!$B$5:$B$35))*(INPUT!$G$5:$G$35='2013'!$B10)*(VALUE('2013'!$B$2)=YEAR(INPUT!$B$5:$B$35)),0)),0) CSE

Personally, I would use SUMPRODUCT. Ideally, define Dynamic Named Ranges so that you only operate on cells with data rather than a fixed column length.

But, better, convert the input data to a Table and then you can use structured Table references, albeit still with SUMPRODUCT.

See the attached updated workbook.

Regards, TMS

how would you use a sum-index-match functions in this workbook?

Not a clue. I wouldn't do it and see no value in looking for a less than optimal solution.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Thank's for responding AZ-XL
I could not make your formula work - the result is 0. Can't see whats wrong.

I could not make your formula work - the result is 0. Can't see whats wrong.

The CSE means use Ctrl-Shift-Enter to commit the formula, rather than just Enter, as it is an Array Formula.

Copy and Paste the formula into the Formula bar and then press Ctrl-Shift-Enter.

Regards, TMS