Note to self • 2 April 2008 • The SnowBlog

Note to self

It's highly unlikely that anyone except me will find this useful. But I have to admit that sometimes I use this blog to make a note just for myself to refer back to later. Evil, I know. Anyway, if you want to know some arcane function in Excel, click on. This: =IF(ISERROR(VLOOKUP(F42,Sheet1!B:F,5,FALSE)),"-",VLOOKUP(F42,Sheet1!B:F,5,FALSE)) is very handy. A normal IF function looks like this: =IF(VLOOKUP(F54,Sheet1!B:F,5,FALSE),VLOOKUP(F54,Sheet1!B:F,5,FALSE),"-") That means: IF the Vlookup function is true, return the result of the Vlookup, otherwise return a dash in the cell. It looks more complicated than it is - it's simply saying if(this logical test is),(true, return this value),(false, return this value). However, if the test returns an #N/A, you have to manually delete the cell, because it won't sum if it's in a range of cells that needs to be summed. And in a scale out, which is what I mainly use Vlookups for nowadays, that takes about a minute. A minute that I could be profitably using looking out of the window, or petting the cats. So naturally it must be eliminated. So by inserting an ISERROR function as in =IF(ISERROR(VLOOKUP(F42,Sheet1!B:F,5,FALSE)),"-",VLOOKUP(F42,Sheet1!B:F,5,FALSE)) You can get rid of the #N/As automatically. Magic! Oh, no-one's reading this. Em of the future, when you search for this post next time you do a scale out: just remember that the ISERROR function will get rid of those annoying #N/As.


The SnowBlog is one of the oldest publishing blogs, started in 2003, and it's been through various content management systems over the years. A 2005 techno-blunder meant we lost the early years, but the archives you're reading now go all the way back to 2005.

Many of the older posts in our blog archive suffer from link rot. Apologies if you see missing links and images: let us know if you'd like us to find any in particular.

Read more from the SnowBlog...

« Happy Birthday to us
Snowbooks Rights Guide 2008 »