Does A-Z Sort Speed VLOOKUP?
September 23, 2013 at 9:39 am Bill Jelen Leave a comment
I’ve written in many books, when you are doing the “,FALSE” version of VLOOKUP, the lookup table does not have to be sorted.
However, every day, I see the same tweet claiming otherwise:
I asked the Tweet’s author if they thought this held true for the FALSE version of VLOOKUP. They replied yes, it was still important to sort A-Z particularly when you have 60,000 row lookup tables.
In Podcast 1790, I used some formula-speed macros to test the various scenarios. With a 100K+ row lookup table and 14K VLOOKUP formulas, a regular unsorted VLOOKUP took 82 seconds. Sorting the data A-Z slowed the recalc time to 113 seconds! Hence, the A-Z myth is busted.
Also in the video, I theorized that the only sort that could speed up a ,FALSE version of VLOOKUP is if you sorted the best-selling items to the top of the list. Sorting in that manner reduced the recalc time to 9.46 seconds! However, as of yet, there is not a “Sort by Popularity” icon in Excel, so this type of sort might require more analysis, pivot tables, VLOOKUP, and sorting.
The video concludes by testing the Two-True-VLOOKUP beats One-False-VLOOKUP article written by Charles Williams for VLOOKUP Week. Those results were astounding. By sorting the data and using two TRUE VLOOKUPs, the recalc time was less than a second:
- Sorted, =VLOOKUP(A2,Table,2,False) took 113 seconds
- Sorted, =IF(VLOOKUP(A2,Table,1,False)=A2,VLOOKUP(A2,Table,2,False),”Not Found”) took 0.19 seconds
Read Charles original article here.
In the YouTube comments, Matthew asked about replacing VLOOKUP with INDEX/MATCH. I tried this and INDEX/MATCH was 1.5% slower than VLOOKUP. This happens because there was only one column of VLOOKUP. If you had to do a VLOOKUP for each of 12 monthly columns, then one MATCH and 12 INDEX columns would be must faster than 12 columns of VLOOKUP.
Table of Results:
Entry filed under: Excel Formula, Excel Function, VLookup, VLOOKUP WEEK. Tags: Charles Williams, Twitter.
Trackback this post | Subscribe to the comments via RSS Feed