• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What's the best way to do a lookup?

dronka

dronka

Member
I have a very large data set (38,000 rows), and I have a lot of columns using lookups from reference tables on other sheets. I'm currently using Index. When I do anything in the workbook, it takes about 5 seconds for it to process. I'm wondering if Vlookup is quicker. Or is there another lookup function that's better for large data sets? The reference tables that the lookup is referencing are relatively small (the largest is 1,600 rows).
 
Hi David,

Looking at 38000 rows 5 seconds is fast ;).

Vlookup can be used based there are no duplicates in the lookup value other wise you have to include one extra helper column which ofcourse is a formula. Just see if there are any volatile functions in your sheet, also looks for CF in your sheet. Even Ref like $D:$D that is whole row or column can also slow down the calculation.

Regards,
 
Last edited:
The highest increase in speed you'll get is if the lookup table is sorted and you're not worried then about a lookup value not being found. Sorted data is much easier for XL to look for values in.
 
You must log in or register to reply here.
Back
Top