# Optimize calculation speed

#### 2Ruff4U

##### New Member
I have a rather large spreadsheet that takes a very long time to calculate once the new data is added. One of the many things I need to do is look to see if a unique value in range 1 is also in range 2. If it is, return some data (vlookup), if it's not, then I want a "0", not #NA. My question is, which of these two methods will result in a faster calculation (if at all):

Option 1: Do it in one step
=IF(ISNA(VLOOKUP(\$A3,LY,3,FALSE)),0,VLOOKUP(\$A3,LY,3,FALSE))

Or option 2: Do it in two steps:
Column N formula:
=VLOOKUP(\$A3,LY,3,FALSE)

Column N+1 formula:
=IF(ISNA(Column N value),0,=column N value)

I have many columns of data using formulas in option 1, so if I have coded this badly that could be my problem...

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### schielrn

##### Well-known Member
My first question is what is the range for the named range LY? Also can LY be sorted in ascending order, or is that not feasible?

#### 2Ruff4U

##### New Member
LY is data from last year. The lookup formula is in the "this year" section and checks to see if a unique identifier this year also appears last year. The last year data is 5 columns wide (as is the this year data) and can range from 1,000 to 100,000 rows, depending on the client.

##### MrExcel MVP
What is the actual range LY stands for, and is this range sorted in ascending order on its first column?

#### 2Ruff4U

##### New Member
The Range for LY is always A3:Ex on a separate tab (where "x" can be anywhere from 1,000 to 100,00). The data file I import does not come sorted, but I could sort it either before or after the import...

##### MrExcel MVP
The Range for LY is always A3:Ex on a separate tab (where "x" can be anywhere from 1,000 to 100,00). The data file I import does not come sorted, but I could sort it either before or after the import...

If you can sort it in the said manner and keep the data that way, then invoke:

=IF(LOOKUP(\$A3,TableTab!A:A)=\$A3,LOOKUP(\$A3,TableTab!A:A,TableTab!C:C),0)

=IF(ISNA(VLOOKUP(\$A3,LY,3,FALSE)),0,VLOOKUP(\$A3,LY,3,FALSE))

#### 2Ruff4U

##### New Member
Thank you for your reply. I assume both of our solutions ultimately accomplish the same thing, are you suggesting that your formula will be less of a drag on the processing time of the original formula? If so, why?

##### MrExcel MVP
Thank you for your reply. I assume both of our solutions ultimately accomplish the same thing, are you suggesting that your formula will be less of a drag on the processing time of the original formula? If so, why?

The LOOKUP version with the sorted data will be very fast for it will run on a binary search algorithm, while the VLOOKUP version has to use a linear search and moreover must compute things twice.

#### 2Ruff4U

##### New Member
Again, thank you for your insights - I shall now make use of the F2 key...

#### Fazza

##### MrExcel MVP
Hi,

You mention a data file import. This suggests there might be totally different approaches that are suitable - and potentially much faster. Such as not importing all the data and using SQL to do the work currently done by formulas. Or even if the data is imported entirely, still using database type approaches - that is not using formulas. With large amounts of data large number of formulas can be slow.

Of course these are only very general comments. To be able to give more specific advice requires an understanding of the tasks being done.

I recall at least one example in past forum threads where formulaic approaches took over an hour to calculate whereas non-formula database type approaches took about a second for the same result.

Regards, Fazza

Replies
5
Views
123
Replies
7
Views
440
Replies
4
Views
324
Replies
10
Views
301
Replies
6
Views
220

1,190,652
Messages
5,982,124
Members
439,756
Latest member
alice128

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back