Quick post. I was writing up the Double-VLOOKUP trick I learnt from Charles Williams for the book.

Standard VLOOKUP on unsorted data:
=VLOOKUP([@ID],Table2,2,FALSE)

Double VLOOKUP trick on sorted data:
=IF(VLOOKUP([@ID],Table2,1,TRUE)=[@ID],VLOOKUP([@ID],Table2,2,TRUE),NA())

The point of the double VLOOKUP trick is this:

• Standard VLOOKUPS on unsorted data are slow, because your VLOOKUP has to look at each item in turn until it finds a match. So on average, it looks at – and discounts – half the things in your lookup list before it finds that match.
• Binary searches are lightning fast. Because your data is sorted, they can start half way through the lookup list, and check if the item at that point is bigger or smaller than what they’re looking for. Meaning they can ditch half the list immediately, then look halfway through the remainder. And over and over, ditching half the list each time until they either find the item they are looking for, or rule out all items.
• VLOOKUP and MATCH will quite happily do a binary search for you. But for reasons known only to Microsoft, they offer an exciting plot-twist: if they don’t find what you’re looking for in the list, they return the closest match they can find to it, which happens to be the only thing left in the list when they’d divided it in half enough times. Only they don’t tell you its not an exact match. Phooey.
• Charles’ brilliant trick is to do two lightning-fast Binary searches – the first one simply looks for the closest match to your input term among the Lookup terms. If it’s an exact match with what you fed it, you know your lookup term is in the list. So now that you KNOW it’s there for sure, you kick off a second approximate match VLOOKUP, which will grab the corresponding value you want from the lookup table. Go read his blog for the specifics.

I’ve known about this for a while, but it’s only as I’m writing this up for the book that I’ve gained an appeciation of just how much faster Binary Searches – and the Double VLOOKUP trick that gets around Microsoft’s crap implementation of them – are, compared to unsorted/linear VLOOKUPs.

See for yourself:
 

 
 
That says that:

• At one extreme, if your lookup table has 10,000 things in it, the double VLOOKUP trick on sorted data is 28 times faster than the standard VLOOKUP on unsorted data
• At the other, if your lookup table has 1,000,000 things in it, the double VLOOKUP trick on sorted data is 3,600 times faster than the standard VLOOKUP on unsorted data

Kinda puts the ‘hassle’ of sorting your lookup table ascending into perspective, don’t it!

Thought I’d share my two favorite pranks with you.

Number one: Take a screenshot of an MVP from their own website, subtly change it somehow, then email it back to them saying “Did you really say this, or was your site hacked”.

Here’s an example. Spot the original.

Number two: Take a screenshot of someone’s spreadsheet next time they walk away from their desk, then paste it neatly over the real thing – like this one that I’m in the process of doing:

 

 
 

Hopefully you’ll catch them before they quit Excel forcibly using End Task. (But don’t worry if you don’t…they’ll be none the wiser.)

My only problem is that I’m forced to play this last prank on myself, which kinda ruins the surprise. But I’ve still got that first one to get me through.

Any new Excel-related April Fools pranks out there from anyone? I still have a chapter to write of my Evil Genius book on arrays or something unimportant like that, but I’m running out of steam. So I’ll just put your nastiest pranks in there instead. That outta help folk out with career progression, one way or the other…

I wish more Excel blogs had a global site-wide comments feed that you could subscribe to, like this one does. (Daily Dose of Excel: http://www.dailydoseofexcel.com/comments/feed/)

Comments are where the party is. Sure, you can subscribe to comments on blogs post by post, but sometimes the article doesn’t seem that relevant to you at the time, so you don’t bother. And then someone posts some gem that you wished you had known about. And you missed it, because it wasn’t in the original piece.

And on a site like this one that predates my interest in Excel by like over a decade, I find comments from the Comment Feed often alert me to helpful stuff I’ve missed. But retrospectively subscribing one post at a time in order to catch them ain’t a serious option. So a global comments feed is one of the handiest ways for people to consume great content – often NOT written by the original author – for years to come.

Here’s the site-wide comments feeds for the main sites I know about that have ’em:
Chandoo: http://feeds2.feedburner.com/CommentsForPointyHairedDilbert-Chandoo
Contextures: http://feeds.feedburner.com/ContexturesBlogComments
ExcelXOR: http://excelxor.com/comments/feed/
Newton Excel Bach: http://newtonexcelbach.wordpress.com/comments/feed/
Bacon Bits: http://feeds.feedburner.com/CommentsForBaconBits
PowerPivotPro: http://www.powerpivotpro.com/comments/feed/
Excel & UDF Performance (Charles Williams): http://fastexcel.wordpress.com/comments/feed/
YourSumBuddy: http://yoursumbuddy.com/comments/feed/
MyOnlineTrainingHub (Mynda Treacy): http://www.myonlinetraininghub.com/comments/feed
Jon Peltier: http://feeds.feedburner.com/CommentsForPtsBlog
RAD Excel (Colin Legg): http://feeds.feedburner.com/CommentsForRadExcel
Andrew’s Excel Tips: http://andrewexcel.blogspot.com/feeds/comments/default

My favorite used to be the Bacon Bits comments feed: most of those comments were as risque as Mike’ articles. But then he had to go spoil things by adding a spam filter.

Ah well…the Captcha almost makes up for it:
 

Anyone else have any great global comments feeds on the main Excel sites out there worth sharing?

Finding non-VBA alternatives for the volatile TODAY and NOW functions is tricky, but here’s an example of some creative workarounds for the Evil Boss’ report generator.
 

 
 
That last one that uses a dropdown I pulled from a at the ExcelHero LinkedIn group . It’s pretty clever: what you do is create a small Table somewhere, and populate it using the naughtily volatile =TODAY() function, as well as the words “Please choose…” .
 

 
(An alternate wording to “Please choose…” would be “Choose wisely…”).

Then you point a dropdown list at that Table. And then you assign the name TodaysDate to the cell that the dropdown lives in, and use that instead of TODAY() throughout the rest of your workbook.

The beauty of dropdowns is that while they may reference that volatile function, as soon as the Evil Boss makes a choice, the choice gets written to the cell as a string, and not as a reference to that volatile function. So it IS today’s date, but it is NOT volatile. Of course, you need a big obnoxious message pointing at it so that:

1. The Evil Boss remembers to fill it out today; and
2. The Evil Boss also remembers to update it tomorrow

But obnoxious is my middle name.

One of the key premises of my book is that Excel has many passionate online user communities. And on that subject, I say:

If you don’t utilize them, you are missing out. If you tap into them, your work isn’t just based on what you know, but on what the collective we know. Which is pretty much everything, between collective us.

One of the things I cover in the book is how to get great results out of a help forum. Ah, but which one?

In the past, I’ve spent some time hanging out at Ken Puls’ ExcelGuru forum – although not too much for the last couple of years. More recently, I’ve been hanging out on the Chandoo forum, although not for the last 6 months due to spending more and more time on the book.

I guess why I started hanging out on the Chandoo forum more and more was that the volumes of questions were good but not overwhelming, meaning you could not only keep an eye on all the questions, but all the answers. Which really fostered a great little community among the regular respondants, I thought.

Of course, I always end up on the Mr Excel forum when Googling – and when I still had my VLOOKUP training wheels on, I learnt some incredible things there from the likes of Fazza and Aladin Akyurek (who has taken part in 68k posts, would you believe). But I’ve never spent much time monitoring content there, largely because of the overwhelming volume of it.

Increasingly I find Google directing me to Stack Overflow. For some reason I don’t really ‘get’ SO. I find it kind of scary looking, from a newbie’s perspective. Anyone feel the same? (Obviously not Dick – he’s been hanging out there for 6 years 6 months, and was last seen there 10 hours ago. Not that I’m stalking.)

Anyways, what forum would you recommend, Hive Mind, and why? I haven’t finished this part of the book yet, and would appreciate your inspiration and wisdom, if you have some to contribute.

Well THAT sure got your attention.

I was doing a bit of research for the book (the only bit, mind), and I came across this great old thread on VLOOKUP vs INDEX/MATCH.

There’s a few false starts in terms of test methodology until Jon von der Hayden puts things back on the right track about halfway through the thread.

Conclusion is that INDEX/MATCH is probably way to go, but there’s not a great deal in it.

But I think there’s another important point in the INDEX/MATCH combo’s favor that has been overlooked in this thread: and that’s this bit in bold:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The fact that a VLOOKUP functions points at an entire table of data – even though it only ever actually makes use of two columns of it (the key column on the far left, and the nth column given by the col_index_num argument) – means that any time you change any cell in that table, your VLOOKUP is going to recalculate. And then so are ALL formulas downstream of those VLOOKUPS. It’s exactly as if VLOOKUP is a volatile function, where changes to that that Table is concerned.

Whereas with INDEX/MATCH, your functions will ONLY recalculate if you change something in the specific columns that the INDEX and MATCH combo references.

Given that, I’ll take INDEX/MATCH any time.

One thing still puzzles me hugely about VLOOKUP though: the recalculation time incurred from updating the lookup table seems much much longer than the recalculation time incurred from changing an input parameter for the VLOOKUP itself. And the same goes with INDEX/MATCH.

Take the situation where you’ve got a VLOOKUP and Table that looks like this:
 

 
 

The KEY column is just the numbers 2 through 1,048,576 sorted in random order. And the Value column is just the row number. I won’t post a sample file because it runs to about 20MB, but it’s easy to set up yourself in about 20 seconds.

Changing the orange input cell triggers the VLOOKUP, which gets the right answer pretty quick. I haven’t timed it, but we’re talking ‘blink of an eye’… even when I tell it to look for a number that I happen to know is right at the bottom. Really fast.

But if I type anything in that middle column in the Table – which also causes our VLOOKUP to recalc – Excel acts really really sluggish. Maybe a second of delay in some cases, plus it makes my screen flash.

Which is weird, because it’s the same one VLOOKUP that gets triggered in both cases.

Why would that be?

—UPDATE—
If you haven’t already, go and read Charles Williams’ awesome article Developing Faster Lookups – Part 1 – Using Excel’s functions efficiently – updated

Look for the bit “Large dependency ranges trigger unnecessary Lookups”.

Read it, and don’t weep.

I’m getting close to finishing off the first draft of my book, and I’m putting some goofy images at the start of every major section, to lighten it up. I want this to be a laugh-out-loud Excel book: my goal with these images is to make some schmuck reading an Excel book snort out loud on the train during their commute, so that fellow commuters figure this is one sad individual.

In fact, I’m hoping more people will buy it “just for the pictures”, like my mother has promised to. Which is why I put an attractive picture of my wife and I on the cover:

So anyways, while I was putting some sample images and text together – all the while snorting out loud to myself at the endless possibilities for entertainment that random animal pictures combined with Excel witticisms provide – I thought why not get you lot in on some of the fun as well. And so I’ve cooked up a grand scheme: the great “Your Name Here” competition.

So here’s the deal: below are some of the sample images I’ve put together so far, using comp images I’ve downloaded from iStock. (I’m sure that my publisher Bill Jelen is going to choke on his morning podcast when he sees I’m serious about some of these).

Your mission – should you choose not to simply laugh it off – is to improve on these in some way if you dare. (I was going to say if you can, but I can tell by your face that you have innate comic ability).

By ‘improve on these in some way’, you’ve got two options:

1. Come up with a better caption with your name in it; or
2. Come up with a better image a better caption with your name in it.

Prize? Oh yeah…hmmm…okay, let me think. How about you get to win a copy of my as yet unfinished book. With that winning picture in it. And caption. With your name in the caption. Just your first name, mind, given that tens, possibly twenties of people will see your name next to some random monkey picture or whatever, and wonder what it means in terms of your Excel prowess. Can’t drag your good name down with mine, can we….

Couple of rules:

 
1. Entries close at the end of this Month (March 2015), and I’ll endeavor to review submissions and get back with some winners and honorable mentions within a fortnight after that.

 

2. If you just want to just submit a caption, don’t email it to me, but instead simply put it below in the comments along with the heading number and name its’ associated with, so we can all share your mirth right away. (You don’t have to put your email address in the comment, because this blog keeps track of your email when you post a comment, so I’ll be able to contact you if you have a winning entry).

 

3. If you want to provide a different image than the ones below as well as a caption, feel free. But please ether use a free image from WikiMedia and include the link in your submission, or a free comp image from iStockPhoto, again with the link. And I need an aspect ratio along the lines of these ones i.e. wider than they are tall like the samples below, so they will fit where I plan to put them in the book, and so that I can purchase the image if it’s going to be used.

   Also, if providing an image, make sure it’s of an animal – the wilder the better – so that it fits with the general theme of the other pictures I’ll be using in the book.

 

Images 6 and 11 below come from Wikimedia. Simply Google WikiMedia and some animal name to see what’s available there. Here’s an example, that shows a Monkey taking a selfie. (I kid you not):

The rest came from iStock. What’s iStock, and what’s a comp image? Just head to www.iStockPhoto.com, type something like ‘Animals’ or what-not in the big search box on the home page, and when you get to the search page, refine it further by unchecking illustrations, video, and audio options:

Then when you see something you want to use, click on it, which will bring it up bigger:

Then you can either click that ‘Get a comp’ option and copy the image that comes up, or simply right click the image shown above and select ‘Copy as Image’ and then paste it into this template and add a caption:
Competition Template

Then simply email it to me at excelforsuperheroes@gmail.com

Have fun, people.

Here’s my sample images, with placeholder text/names. But first, a disclaimer

Disclaimer

All physical characteristics appearing in this work are fictitious. Any resemblance to real MVPs, past or future, is purely coincidental.

1. Chris

2. Rob

3. Ken

4. Jeff

5. Jon

6. Doug

7. Ian

8. Deb

9. Dick

10. Mike

11. Charles

12.Oz

13. Mynda

14. Dave

Even though you’ve seen Excel’s Formula Intellisense a million times, I bet there’s a few things that you don’t know about it that can make your life even easier.

Let’s first look at an example for one of Excel’s in-built functions. Say we want to use an IFERROR function. As soon as we type the = sign in the toolbar and the opening ‘I’ from IFERROR, Excel provides a helpful list of functions that start with the letter ‘I’, as well as a pop-up tool tip telling us what the currently highlighted function from that list does. That popup tool tip is often in the way, but you can move it somewhere else simply by clicking on it and dragging it to the naughty corner.
 

 
 
To select the IFERROR function from that list, we can either use the arrow keys to navigate down that intellisense list, or we can left-click on the particular function we want:
 

 
 
…or we can continue typing, which allows Excel to gradually narrow down the number of functions until there is only one possible choice…the IFERROR function we’re after:
 

 
 
At this stage, I almost always push Enter in order to get Excel to populate this one remaining function in the intellisense list into the formula bar. And then I almost always swear out loud, because Enter is the incorrect key, and because Excel has never heard of an ife function, it asks me for my name, so that it can pass it on to the ‘re-education’ team at Microsoft:
 

 
 
The correct key we need to push is the TAB key, which tells Excel to fill out the rest of the IFERROR name for us. After it’s done this, Excel then helpfully prompts us for the two arguments of the IFERROR function with another handy pop-up tool-tip:
 

 
 
Now let’s compare that to the options Excel gives us for a UDF. I’ll use my JoinText UDF. Typing =j pops up a very short list indeed: there are no other functions starting with J. But the UDF’s name is all we get. There’s no tooltip remindingus what the JoinText function actually does…
 

 
 
…and after we select it by hitting that TAB key, there’s no tooltip prompting us for the function arguments:
 

 
 
So why don’t UDFs have intellisense? That’s a damn good question, and one that programmers have been asking Microsoft about for years. If you can’t remember the arguments for your UDF, you won’t be getting very far. Unless you happen to know this handy little trick: If you push CTRL + SHIFT + A at this stage, Excel helpfully inserts placeholders for all the argument names for that function into the formula bar:
 

 
 
…which you can then overtype with the actual arguments, now that you’ve been reminded what they are. How cool is that!

This trick also works with Excel’s native functions, too:
 

 
 
But the problem we still have with that UDF is this: Which arguments are optional? In the case of the VLOOKUP above, we can see this by the square brackets around the optional [range_lookup] argument in the tool-tip. (As if that argument is optional in the real world…)

But with our UDF, we don’t get that tool-tip pop-up.

Sure, we could just put the prefix opt in front of the optional arguments in the VBE:
 

 
 
…which would give us this:
 

 
…but where’s the fun in that?

It would be cool if we could simply add square brackets to our variable names back in the VBE, so that when we do our Ctrl + Shift + A trick, those arguments look like this:
 

 
 

…but it won’t compile:
 

 
 

So what other interesting characters can we use in VBA variable names? According to this thread, these ones:
 

 
 
Any character in the range 128-255 is allowable. That’s right, you can copy-write and trademark your variable names if you want!

And a couple of them look quite bracketish. Let’s try them:
 

 
 
Yup, that compiles. And how does it look when we Ctrl + Shift + A that sucker?
 

 
 
Hmmm. Works a treat.

Down side? Don’t know. You tell me…