How do I convert an Index/Match formula with multiple criteria to VBA

[KristinaT]

I have been tasked with converting this formula into a user defined function in Excel. I haven't used index/match previously and have no experience with the functions so I am lost as how to convert. I was able to create a functions using a loop which returns the correct value but the data set it large so the process is slow. Can anyone help with getting this converted or with another method that may be more efficient to match six criteria and return the sum of amount?

=-IFERROR(SUMIFS(INDEX(Financials!$A:$M,,MATCH("Sum of Amount",Financials!$A$1:$M$1,0)),INDEX(Financials!$A:$M,,MATCH("Week Number",Financials!$A$1:$M$1,0)),MAX(INDEX(DateSelection!$A:$XDB,,MATCH("Week Number",DateSelection!$A$1:$XDB$1,0))),INDEX(Financials!$A:$M,,MATCH("Company Code",Financials!$A$1:$M$1,0)),Company,INDEX(Financials!$A:$M,,MATCH("Operations Statement Level",Financials!$A$1:$M$1,0)),$A9,INDEX(Financials!$A:$M,,MATCH("Activity Object",Financials!$A$1:$M$1,0)),"K.987084"),0)

Thanks for your help
Kristina

 

[DanteAmor]

Can anyone help with getting this converted or with another method that may be more efficient to match six criteria and return the sum of amount?

I recommend a macro that performs the calculations in memory and puts the results. The processes in memory are very fast, they can even be immediate, it depends on the number of records on your sheet. To try to understand your formula and get the results, it would be best if you provide us with a file with sample data.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.