Excel performance using Vlookup - Excel 2013 and 2016

sunnybank

New member
Joined
Feb 14, 2017
Messages
2
Reaction score
0
Points
0
Hi

I wonder if someone out there might be able to help me regards to vlookup performance with different versions of Excel.

I've been told that later versions of Excel are more CPU intensive, but all I can see is that all versions of Excel can be CPU intensive when calculating - I see CPU time reduce with later versions.

We have a spreadsheet with approximately 800,000 vlookups to another sheet in the same spreadsheet. The complaint is that Excel or the configuration of Excel is not performing as they believe it should, I'm just not so sure. Should excel be able to handle this volume of lookups in an acceptable time, naturally the more CPUs the quicker the calculation

Below is an example of the lookup we are using "=VLOOKUP(B2,'VS billed'!C:G,5,TRUE)".

Any input would be greatly received
thanks in advance
 
You could try changing the VLOOKUPs to INDEX MATCH. The advantage is that with INDEX MATCH, only two columns need processing, whereas with your VLOOKUP above, there are five. Additionally, do not use whole column references (e.g. C:C) - limit the arrays instead (e.g. C$2:C$5000).
 
OTH 800000 is a large number even for INDEX/MATCH
Perhaps post a small sample of your data and expected results
 
Interesting, Paul. I can't imagine why it would make any difference. Personally I hate tables, but horses for courses, as they say.
 
It certainly has an effect when people are otherwise using C:C because it dynamically limits the range to the size of the table. I'm a bit of an anorak when it comes to using tables.
 
I can see that, but I have already advised about limiting the arrays. I assumed you were extolling some additional benefit! :)
 
Ali, As long as you make your table headings as meaningful as possible the use of tables does make the formulas much more readable. With the stuff I do it almost makes it self documenting. It's also easier than using dynamic named ranges. Paul
 
Well, Paul, that's a matter of personal preference. Many people (like me) prefer to do it the old way. What I was asking was why would it make anything any quicker, and you've answered that by saying that it limits the arrays. Limiting arrays can be done several ways: (1) hard code the array as I suggested above, (2) use dynamic named ranges or (3) use a table.

I think Pecoflyer is right, though: the sheer size of this dataset might be the issue.
 
Thanks Ali

I'll give it a go, not a super Excel user myself
 
Depending on desired outcome PowerPivot might be an option
 
Back
Top