Does A-Z Sort Speed VLOOKUP?

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:

 

Advertisements