CODEWITHSUNDEEP
sqlexcelvlookup
Daniel o Keeffe
Daniel o Keeffe

Reputation: 578

Excel Vlookup too Slow

I have an Excel file that uses a vlookup on around 10000 rows, 8 Vlookups per row. Its ridiculously slow.

It looks up another excel file located online - 

=IF(ISERROR(VLOOKUP(E12,'Filepath.xlsx]Data'!$U:BV,54,0)),"None",VLOOKUP(E12,Filepath.xlsx]Data'!$U:BV,54,0))

I've looked around and vlookups seem to be pretty necessary. Are there possible structural changes which could make this go faster, such as vlookups on an SQL database?

Thanks

Upvotes: 1

Views: 4154

Answers (1)

barry houdini
barry houdini

Reputation: 46341

As per my comments, using a LOOKUP formula with a sorted lookup range is significantly faster than using VLOOKUP, more than 100 times faster in this case, e.g. doing some speed tests if I use this formula

=VLOOKUP(B3, $D$3:$D$10002, 1,0)

and then with G3:G10002 containing the same values as D3:D10002 but sorted ascending this LOOKUP formula

=IF(LOOKUP(B3, $G$3:$G$10002) = B3, LOOKUP(B3, $G$3:$G$10002), NA())

the formulas produce exactly the same results but the 2nd formula is consistently 120-140 times faster than the first because of the benefits of "binary search"

If I use the INDEX/MATCH equivalent, i.e.

=INDEX($D$3:$D$10002,MATCH(B3, $D$3:$D$10002,0))

then that's actually marginally slower than the VLOOKUP version, although, as Doug says, if you are using the same MATCH formula in multiple calculations then you can calculate that once only for multiple results so that may be more efficient than VLOOKUP over all

Of course there may be other factors in your setup which are slowing down the calculations.....

Upvotes: 3

Related Questions