Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV

CORRECTION

Several people pushed back on the video I release this morning and I want to thank them (and in particular Wim Gielis). This caused me to open my laptop on the train while on the way to a client’s office (so I could see if I was wrong … which I was!). When I realized I was wrong, I became so deep in thought I missed my stop. And that’s when my phone died. So I have a major apology to make. A few months back I created a solution that shrank the size of arrays my client was doing SUMIF and COUNTIFs on. I used a method similar to the one presented in the video I released today. It made their spreadsheet much more manageable. 

But I wanted to adapt it to make it easier to understand. And, in doing that, I really lost that VLOOKUP already has a faster way to operate. So, the video I presented, while interesting in its technical solution is not the BEST way. The best way would be to change the VLOOKUP parameter to false. Anyway, I think it’s important to learn from mistakes. I don’t plan to take this video down because I don’t want to hide behind my error. But I will add a note at the beginning of the video later today when I am back home. 

Thanks to everyone who watched it anyway. My desire for a complicated solution is sometimes so great that I miss what’s already in front of me. 

Wim Gielis, wanted me to also add this note: “an approximate lookup can lead to a wrong result. All people looked up should be part of the database. Or test for it. Second caveat, database must be sorted on names.”I’d still invite you to watch the video here. Don’t judge me too harshly. Thanks again,Jordan

Making Excel Faster

In this all new episode of Excel.TV, I show how you can make your spreadsheets wicked fast with Excel using VLOOKUP. 

Those who know me know I love the advanced stuff. And this tutorial could have come out of my book Advanced Excel Essentials. 

It works like this. Most of the time, when we use lookup formulas, we grab the entire table range we’re interested and perform the lookup on that. 

The Problem with Lookups in Excel

Lookups in Excel essentially work like this: they start at the top of the cell range and check if that cell is a match. If it’s not, it moves on to the next one. We would say then that in its worst case scenario, it would have to look through a total of n-items. That is to say, if the size of the first column has n items, then we can think of its performance bounded by the nth item, since it would have to go through all items at worst. 

We can dramatically reduce that n by decreasing its search space. But how? 

Enter the Lookup Table

I compare the lookup table to a phone book (remember those?). You don’t search the entire phone book for the person or business you’re looking for. Rather, the phone book is broken down to a section for each alphabetic letter. And all information is stored in alphabetic order. This dynamic essentially helps you avoid looking where you know the information isn’t found. We can do the same in Excel by finding where each new letter in the list starts and then creating a dynamic range based on those boundaries. 

It’s all in the video!

Inasmuch as I would love to write a tutorial here too, this is one of those cases where you’ll learn more just by watching. There are many moving parts and the video brings them all together.

Get the download file!

Want to try it yourself? Click here to get the download file!

Leave a comment!

What techniques do you have that could speed up Excel? Let us know in the comments. 

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)

Tags


You may also like

February 17, 2013

Are Your Formulas All "Wrapped" Up?
  • I appreciate the fact that you made the correction and posted that. It takes a lot of courage.
    Whenever I post some content, I think many times whether it is the best method out there and what if it is not right.
    Best wishes to more videos and tutorials.

  • Cool idea. You “just” have to think outside of the box to come up with these nested formula ideas.

    Where is the link to the next article … ?

    Why not cheat a little and instead of calculating the end row, why not just use the row number for the start of the next letter bin (-1)?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >