Solved

lookup in excel very slow

Posted on 2012-03-17
6
563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 93

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 93

Expert Comment

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

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

626 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