replacing VLOOKUP with INDEX MATCH function

[jrtaylor]

I've got a large spreadsheet which is quite slow to calculate so I'm going through it with the intention of replacing some VLOOKUP and SUMIFS functions with INDEX MATCH functions.

Can anyone help me translate this formula into an Index Match formula?

Please Login or Register  to view this content.

Here's another formula which I suspect is another CPU hog (it is repeated 5000 times down one column). It's a SUMIFS which calculates the number of open trades during various time periods:

Please Login or Register  to view this content.

Can SUMIFs be rewritten into index-match?

If I need to post an example of a spreadsheet which contains a few rows of these formulas just let me know and I'll be happy to.

Thanks!

[oeldere]

If I need to post an example of a spreadsheet which contains a few rows of these formulas just let me know and I'll be happy to.

Please do so, so i can show you the strenght of pivot table.

[e.isayev]

Your formula can be replaced with index match but I dont think that there will be difference in speed.
Array formulas may slow your CPU which is not the case

[Vinoth Kanna]

I have modified the first Vlookup for you.
INDEX($L$7:$N$13,MATCH(A86,$L$7:$L$13,FALSE),2)

For more details on this pl read the following article. It will provide a detailed information about your question -
http://www.mrexcel.com/articles/exce...ndex-match.php

[jrtaylor]

Thanks everyone. I've looked at pivot tables a few times but at the moment I want to try to solve all of this with formulas.

Vinoth, thanks for yours. How does FALSE come into play? On my 2010 excel I only see choices of 0, 1, and 2. The formula seems to anticipate a number rather than text.

Any ideas on the SUMIFS formula?

Thanks!