Is anybody checking my work?

[Jon von der Heyden]

I'm all about index match match, to the point i try to use it for everything and just like many people use vba...

VLOOKUP and INDEX/MATCH constructs are of the most over used functions. They are amazing when used properly but I frequently get requests from people to review their spreadsheets and work out why they are so darn slow. A typical culprit is excessive use of linear search lookups rather than binary search lookups, and usually around 80% of the time with small tweaks they can be substituted for binary search lookups.

In my course I illustrate the efficiency of binary over linear. I have a list of 15000 social security numbers (fake ones) and we do a VLOOKUP against a table of 100000 ssn's with associated names, DOB etc. I set Excel to manual calc mode and when I hit recalc I get the class to count how many seconds until calc is completed, usually around 35-42 seconds.

Then I sort the table and use a binary search VLOOKUP (a double-jointed one to emulate #N/A behaviour of linear search VLOOKUP). Again the class counts the recalc time, only this time it's done in less than 1 second.

More on binary/linear search: Extract a number from a text string with LOOKUP Jon von der Heyden

 

[dispelthemyth]

Very good article Jon, very clear explanation of what occurs step by step

 

[Domski]

I have a list of 15000 social security numbers (fake ones) and we do a VLOOKUP against a table of 100000 ssn's with associated names, DOB etc. I set Excel to manual calc mode and when I hit recalc I get the class to count how many seconds until calc is completed, usually around 35-42 seconds.

I ring IT and ask for a faster PC before repeating this and finding it takes no time at all .

Dom

 

[AlphaFrog]

I just got notified today of the Feb 21st replies to this thread. What gives

Nice article on Binary Lookup, Jon. There are several other nuggets on your site as well. The PrankBackfire sub is sick. I like it. BTW; what's your phone number

 

[RoryA]

I think the notif you got was from a Spam post that got tanked.

 

[AlphaFrog]

I think the notif you got was from a Spam post that got tanked.

Ahh. Thanks for the feedback.

 

[dispelthemyth]

It also made no sense so its was not good quality spam

 

[Jon von der Heyden]

Nice article on Binary Lookup, Jon. There are several other nuggets on your site as well. The PrankBackfire sub is sick. I like it.

Thanks. I may one day actually get the time to complete the dozens of partially written articles on the blog. I always start these things and then never have the time to complete.

BTW; what's your phone number

Haha! Stick we the number in the routine! Although I think the kid changed it after 2 weeks of continuous calls.