Cell Calculation speed

[philobr]

Hi there,
I have a large sheet with a number of VLOOKUP formulas in it.

When the data is changed the Cell Calculations kick in and this can take a while.

Is there a way to speed this process up?

Have Excel2003

Thanks.

 

[brans1982]

Use INDEX and MATCH.

They are much faster. Post an example of your vlookup and I'll convert if your not sure how to.

 

[philobr]

Hi there, here is an example:

=IF(ISNA(VLOOKUP(CB2,Sheet2!AH:AJ,2,FALSE)),"",VLOOKUP(CB2,Sheet2!AH:AJ,2,FALSE))

 

[JackDanIce]

One reason it's slowing down is because you're using the full columns AH:AJ.

If you can fix the area you are looking up, say AH1:AJ2000, perhaps even define it as a named range, this would make a marginal difference.

Also, I guess like brans1982 suggestion, you could change your test condition (first part to):

=IF(ISNA(MATCH(CB2,MyArea,0)),"",VLOOKUP(CB2,MyArea,0))

Where MyArea = Sheet2!AH1:AH2000

Since this part of your code is simply testing if the lookup value doesn't exist in the first column of your data (i.e. return ""). Only if it does exist, then return the associated column.

 

[brans1982]

=IF(ISNA(VLOOKUP(CB2,Sheet2!AH:AJ,2,FALSE)),"",VLOOKUP(CB2,Sheet2!AH:AJ,2,FALSE))

The fact your VLOOKUP is looking through 2 columns 65536 (Excel 2003 or less) rows is slowing the calculation down at alot. If you can edit the formula so it's only looking in the rows with data eg.

=VLOOKUP(CB2,Sheet2!AH1:AJ1000,2,FALSE)

That would speed things up quite a bit.

Other wise try...

=IF(ISNA(MATCH(CB2,Sheet2!AH:AH,0)),"",INDEX(Sheet2!AI:AJ,MATCH(CB2,Sheet2!AH:AH,0)))

 

[philobr]

Thanks, I'll try the named range and the INDEX/MATCH.
I put the INDEX formula in and I get #REF value, but I'll see if I can figure it out.

Thanks again.

 

[brans1982]

Hi,

Try...

=IF(ISNA(MATCH(CB2,Sheet2!AH:AH,0)),"",INDEX(Sheet2!AI:AJ,MATCH(CB2,Sheet2!AH:AH,0),1))

=IF(ISNA(MATCH(CB2,Sheet2!AH:AH,0)),"",INDEX(Sheet2!AI:AJ,MATCH(CB2,Sheet2!AH:AH,0),2))

The ",1" or ",2" will dtermine if the value is returned from Column AI or Column AJ.

 

[philobr]

Hi there, thanks for this, I was playing around with it after I was getting the #REF and I used a COUNTIF and VLOOKUP, that actually speeded things up and I also named the ranges.
Thanks for the help, great as usual from the site.