Help Needed Combining Index/Match with Sum across Different Sheets!

[fyparekh]

Hi All, this is my first time posting here. I have done a lot of google searching but i haven't found exactly the problem to my issue. I have multiple sheets which i want to add across. the rows and columns are not in the same order, therefore, a simple sum across sheets wont work. I want to avoid using an (index/match formula + Another index match formula + ....) .. you get what I mean. I have several sheets and i don't want the formula to be that big and cumbersome.

Here is the index match formula i have currently, with "reimbursement" being the name of the sheet. I have several sheets and want to avoid adding multiple of these below forumulas with different sheet names. I think there is a way to do this using indirect/sumproduct/index/match but just not sure how to structure it. Any help is appreciated. Thanks!

=IFERROR(INDEX(Reimbursement!$11:$599,MATCH(SUMMARY!$B12,Reimbursement!$B$11:$B$599,0),MATCH(SUMMARY!Z$11,Reimbursement!$11:$11,0)),0)

 

[sijpie]

I don't think you are going to make it any simpler than stringing on a number of index/match formulas. You could write these up in a UDF so that at least in the cell it looks more clean, but in the background it will do very much that.