What formula (between these two formulas) would use more system resources?

[dikken20]

Hi,
My excel file calculation takes too long, I've been told to change to different formulas in order to use less system resources, thus, calculation will take much less time to run.

Which of the following formulas uses less/more system resources:

=INDEX(Sheet1!$C$2:$G$600,MATCH(M2,Sheet1!$C$2:$C$600,0),5)

or

=VLOOKUP($M2,Sheet1!$C$2:$G$600,5,FALSE)

?

 

[Ron Coderre]

Those two formulas should have very similar performance.
Technically, you could use this:

=INDEX(Sheet1!$G$2:$G$600,MATCH(M2,Sheet1!$C$2:$C$600,0))

But, that won't significantly impact calc time.



I'm guessing the issue is rooted in other formulas.

 

[dikken20]

Those two formulas should have very similar performance.
Technically, you could use this:

=INDEX(Sheet1!$G$2:$G$600,MATCH(M2,Sheet1!$C$2:$C$600,0))

But, that won't significantly impact calc time.

I'm guessing the issue is rooted in other formulas.

Thank you Ron for the reply.
Well, the workbook contain about 12 sheets, while,:
1. Three sheets using hundreds of rows and about 20 columns
2. Nine sheets using about 50 rows and 20 columns
The majority of all of these data in these sheets are Vlookups and some are Large/Small.
There're some conditional formatting in the nine sheets that as well.

Calculation takes about 10 seconds of PC freezing.

Do you think there's something to do to reduce calc time?

 

[Joe4]

Well, the workbook contain about 12 sheets, while,:
1. Three sheets using hundreds of rows and about 20 columns
2. Nine sheets using about 50 rows and 20 columns
The majority of all of these data in these sheets are Vlookups and some are Large/Small.
There're some conditional formatting in the nine sheets that as well.

If you have that much data and that many pages interacting with each other, what you really have is a relational database model.
While you can use Excel for those type of things, it really wasn't what it was designed for, so performance is less than optimal.
Using a program that was designed for relational databases, like Access, Oracle, SQL, MySql typically works better.

 

[RoryA]

The majority of all of these data in these sheets are Vlookups and some are Large/Small.
There're some conditional formatting in the nine sheets that as well.

The red part sounds like it might be array formulas? The blue part will also slow things down if you have CF formatted cells visible at the time.

Also, if your VLOOKUP formulas are returning several columns of data for the same lookup value, you should use separate MATCH and index formulas so that you only do the lookup part once.

 

[dikken20]

If you have that much data and that many pages interacting with each other, what you really have is a relational database model.
While you can use Excel for those type of things, it really wasn't what it was designed for, so performance is less than optimal.
Using a program that was designed for relational databases, like Access, Oracle, SQL, MySql typically works better.

That data is dynamic based on an external CSV file which refreshed every 1 minute and stored on one of the sheets.
So I'm not sure how using a database to store and retrieve data every 1 minute will help in this case I'm afraid...

 

[Joe4]

That data is dynamic based on an external CSV file which refreshed every 1 minute and stored on one of the sheets.
So I'm not sure how using a database to store and retrieve data every 1 minute will help in this case I'm afraid...

Note that you can link CSV files to Access tables, so any changes should be reflected dynamically.
I have never tried that myself, but it seems like it might work.

 

[dikken20]

The red part sounds like it might be array formulas? The blue part will also slow things down if you have CF formatted cells visible at the time.

Here's an example for the red part. It uses less data than the vlookup:
=LARGE(Sheet1!$W$2:$W$600,$A3)

Also, if your VLOOKUP formulas are returning several columns of data for the same lookup value, you should use separate MATCH and index formulas so that you only do the lookup part once.

Do you mean that I should replace the vlookup to the Index/Match but to a different format than the one I suggested (
Instead of both the vlookup and =INDEX(Sheet1!$G$2:$G$600,MATCH(M2,Sheet1!$C$2:$C$600,0))
I should use:
A1=MATCH(M2,Sheet1!$C$2:$C$600,0)
A2=INDEX(Sheet1!$G$2:$G$600,A1)

or did you meant to something else?

 

[RoryA]

Yes, that's what I meant, assuming you need to return more than one piece of information for the lookup value in M2.

Additionally, if you can sort the data on Sheet1 by the lookup column, you can use a binary search vlookup, which is much, much faster:

=IF(VLOOKUP($M2,Sheet1!$C$2:$G$600,1)=$M2,VLOOKUP($M2,Sheet1!$C$2:$G$600,5),"")

 

[dikken20]

Yes, that's what I meant, assuming you need to return more than one piece of information for the lookup value in M2.

Additionally, if you can sort the data on Sheet1 by the lookup column, you can use a binary search vlookup, which is much, much faster:

=IF(VLOOKUP($M2,Sheet1!$C$2:$G$600,1)=$M2,VLOOKUP($M2,Sheet1!$C$2:$G$600,5),"")

M2 is actually just a static value of a stock identifier number.

Let me explain by an example:
Sheet1 stores all data from CSV which I get from the stock market. Column C in Sheet1 stores the identifiers of the stocks, while the following columns store other data (Last price, High, Low, Volume, Change, etc,..)

Sheet2 (for instance) shows the best performing stocks at the moment (it suppose to show like the highest performing 20 stocks).
M2 (in sheet2) represents the highest performing stock identifier.
So if I want to get the Last Price of the stock shown in M2 I would lookup in Sheet1 for the LastPrice column based on the stock identifier (M2).

So I'm not sure how the formula you suggested would help, in fact it will duplicate the vlookups amount