Slow Calculation
 

hello Everyone,

This small formula over 300 rows and 16 columns takes about 20 mins, can I
minimize that?

=IF(ISERROR(VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL
aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL
aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL
aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL
aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL
aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE))

Slow Calculation
 

If you know the maximum number of rows, I would recommend not to use the full
columns in the VLOOKUP ranges ($A:$Z), but use a fixed range instead
($A$1:$Z$5000, for example). Even better, create names for those ranges and
use the names instead.
One other thing that you may try is using the MATCH formula in the IF part
of the formula, if possible also on a limited range. For example of the
first part of the formula could look like:
=IF(ISERROR(MATCH($A7,all_codes,0)),0,VLOOKUP($A7, all_data,'MTL
aux'!B7,FALSE))
where "all_codes" is the range 'CHE all sku'!$A$1:$A$5000, and "all data" is
'CHE all sku'!$A$1:$Z$5000.

Hope this helps,
Miguel.

"JavyD" wrote:

hello Everyone,

This small formula over 300 rows and 16 columns takes about 20 mins, can I
minimize that?

=IF(ISERROR(VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL
aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL
aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL
aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL
aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL
aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE))

Slow Calculation
 

Good advice from Miguel!

In addition: you can halve the number of VLOOKUPs by adding an extra cell, in which you do the VLOOKUP; in the next cell you test
it for ISNA, and return 0 or the result of the VLOOKUP.
Next point: 4th argument FALSE is very, very slow, because the table has to be searched from top to bottom sequentially.
Of course shortening the range, as Miguel suggested, may help a lot. If it still is too slow: can you sort your data? If so, you
can use True as 4th argument (or omit it) and check yourself that the key returned is the right one.
Fastest of all:
Sort the data, use MATCH to locate the row (4th argument = 1), ISNA() to check for presence, INDEX() to pick out the right item.

--
Kind regards,

Niek Otten

"Miguel Zapico" wrote in message
...
| If you know the maximum number of rows, I would recommend not to use the full
| columns in the VLOOKUP ranges ($A:$Z), but use a fixed range instead
| ($A$1:$Z$5000, for example). Even better, create names for those ranges and
| use the names instead.
| One other thing that you may try is using the MATCH formula in the IF part
| of the formula, if possible also on a limited range. For example of the
| first part of the formula could look like:
| =IF(ISERROR(MATCH($A7,all_codes,0)),0,VLOOKUP($A7, all_data,'MTL
| aux'!B7,FALSE))
| where "all_codes" is the range 'CHE all sku'!$A$1:$A$5000, and "all data" is
| 'CHE all sku'!$A$1:$Z$5000.
|
| Hope this helps,
| Miguel.
|
| "JavyD" wrote:
|
| hello Everyone,
|
| This small formula over 300 rows and 16 columns takes about 20 mins, can I
| minimize that?
|
| =IF(ISERROR(VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL
| aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL
| aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL
| aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL
| aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL
| aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE))