Solved

lookup in excel very slow

Posted on 2012-03-17
6
556 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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