VLOOKUP performance issues

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. If there is anything you can suggest to speed up the processing I would greatly appreciate it. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long.

Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

Thanks in advance for your help.

I realized the vlookups were not the problem it was the VBA code.

Here is the concatif funtion

Please Login or Register  to view this content.

Hi jbernhard,

welcome to the forum. However ...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I'll add the tags for you this time, but I'll expect you to use them from now on.

There's a minimal performance improvement by changing these formulas

E3:
=INDEX(N:N, MATCH(A3,M:M,0))

D3: =INDEX(O:O, MATCH($A3,$M:$M,0))

With 2000 of those CONCATIF() formula all trying to recalculate with every change to your sheet (UDFs like this tend to be volatile)... I would be tempted to switch to some sort of active macro.

hi,

To add to JB's suggestion of using some sort of macro (I'm guessing to initiate the calculation process).
Here is a link to some further discussion about design & calculation of UDF's on Charles Williams' site: http://www.decisionmodels.com/calcsecretsj.htm

I suggest reading all of this link (esp the "Automatic and Function key Calculation slower than VBA calculation" section just over 1/2 way down) & the rest of the frames that are linked too.

hth
Rob