Solved

lookup in excel very slow

Posted on 2012-03-17
6
555 Views
Last Modified: 2012-03-18
I use lookup function in a excel file with 34000 files to search the ones that aply to my criteria (e2):

=SI.ERROR(BUSCARV(E2;'dades n-1'!F2:$J$34000;2;false);0)

Buscarv is lookup in spanish

The problem is that i do the same operation, in 3 other colums:

=SI.ERROR(BUSCARV(E2;'dades n-1'!F2:$J$34000;3;false);0)
=SI.ERROR(BUSCARV(E2;'dades n-1'!F2:$J$34000;4;false);0)
=SI.ERROR(BUSCARV(E2;'dades n-1'!F2:$J$34000;5;false);0)

and it is very, very.... slow.

Do you know any other way to do it, via VBA or power pivot tables?

Any sugestion would be greatly appreciated.
0
Comment
Question by:joanaplicacions
  • 2
  • 2
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 245 total points
ID: 37733914
VLOOKUP / BUSCARV is a handy function, but its performance can be bad.

Writing your own VBA function will likely just make it worse: UDFs will underperform compared to native functions.

You could try INDEX and MATCH.  Not sure what the Spanish equivalent would be:


=IFERROR(INDEX('dades n-1'!$F$2:$J$34000,MATCH(E2,'dades n-1'!$F$2:$F$34000,0),2),0)

That replaces your first BUSCARV.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 245 total points
ID: 37734105
I would suggest that you use a separate column for the match function

=COINCIDIR(E2;'dades n-1'!F2:$J$34000;false)

and then use an index function (assuming that the match function is in column F2)

=SI.ERROR(INDICE('dades n-1'!F2:$J$34000;F2;2);0)
=SI.ERROR(INDICE('dades n-1'!F2:$J$34000;F2;3);0)
=SI.ERROR(INDICE('dades n-1'!F2:$J$34000;F2;4);0)
=SI.ERROR(INDICE('dades n-1'!F2:$J$34000;F2;5);0)
0
 

Author Comment

by:joanaplicacions
ID: 37734293
Thankyou very much, I am new in this web but I like a lot, the answers are very good and fast.

Just a litle question for ssaqibh, why it is better to put the mach function in a separated column?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37734473
The coincidir and the buscar functions both search through 34000 rows one-by-one. If you use 4 buscar functions then the entire process is done four times. If you use one match function then the process is done only once.

The indice function knows where to pick the value from (from the extra column) so it does not take time. So any number of indice functions would go unnoticed.

So eventually now you have to bear all the buscars only once for the extra column and the rest is instantaneous.
0
 

Author Comment

by:joanaplicacions
ID: 37734618
Thanks a lot ssaqibh
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37734835
Good point Saqib :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now