Does A-Z Sort Speed VLOOKUP?

September 23, 2013 at 9:39 am 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:

WrongTweet

 

 

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.

VLOOKUPNoPopular

 

 

 

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:

VLOOKUPTableLOOKUPChart

 

Advertisements

Entry filed under: Excel Formula, Excel Function, VLookup, VLOOKUP WEEK. Tags: , .

Live MrExcel Seminar in Daytona Beach or Ft Myers Best Places to Trick or Treat Using Excel Power Map

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Learn Excel from MrExcel

Learn Excel Podcast Splash Screen

The Excel Beginners Book:

"Don't Fear The Spreadsheet" - by Tyler Nash, Bill Jelen, Tom Urtis and Kevin Jones

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 737 other followers

Live Lessons Excel 2010 DVD

"Live Lessons Excel 2010" DVD

RSS MrExcel Recent Articles

  • An error has occurred; the feed is probably down. Try again later.

MrExcel Podcast Archive


%d bloggers like this: