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!
Leave a comment!
What techniques do you have that could speed up Excel? Let us know in the comments.