Spy Journal 3.0 Filtering the web for you

I realised that when I wrote the ISNA function article that I had never written an explanation of VLOOKUP. I want to write up explanations for a umber of other functions in the future like COLUMN, ROW, MATCH and INDEX followed by OFFSET. All these make lots of sense when you use them with the VLOOKUP function. So I though it best to start with this function. I will also say that everything I write about VLOOKUP here applies to HLOOKUP as well. the only difference is the orientation, that is VLOOKUP looks across columns from left to right, and HLOOKUP looks down rows from top to bottom.

The built in Excel Help is very good at helping with this function. However it doesn't point out many of the pitfalls than occur in common use.

Live Mesh announces an update affecting all platforms

Windows Vista SP2 hits the streets today - Sarah has a full list of the enhancements and fixes in Vista SP2

Tony Morgan posts a though provoking piece with 10 reasons why he doesn’t like most Christians. I would agree with them all.

Rodney Olsen posted this cool video about technological and futuristic trends. I have added it here also.

The ISNA function is an Information Function. It is is used to return information about the status of a cell, or specifically another functions results. The most common use I have for this function is to validate the VLOOKUP function. If the VLOOKUP function is looking for a value that can not be found in the lookup range, then it will return #N/A as the result. Thus it is good to wrap that function in an IF Statement using the ISNA function to replace the #N/A results with something else – e.g. a text string such as “error” or maybe a zero.

=IF(ISNA(VLOOKUP(A1,$B:$B,1,FALSE))=TRUE,"error",VLOOKUP(A1,$B:$B,1,FALSE))

Remember the ISNA function returns a TRUE or FALSE. These equate to a 1 or 0 so can be used in formulas such as array formulas as well.

This mind map from Robin Good has to be the best this week  listing free or minimal cost online collaboration tools. Feel free to add missing tools (or contact Robin directly).

This week I have a few selected items from Amit over at Digital Inspiration.

• How to Make a Twitter Background Image with PowerPoint or Keynote
• How to Carry Your Google Reader Offline with Scoop, Supports Synchronization
• Scandoo allows you to check your website for potential misuse in comments etc by spammers. Simply type in your domain name using the site: identifier – e.g. site:spyjournal.biz.

Mostly Lisa has a photography contest – vote for your favourite photo on this page

Dana Coffey has a very good netiquette article – I fully agree with the Facebook application thing!

Xobni (an Outlook plug in I couldn’t live without now) has upgraded and has some cool new features. Their blog post is titled Xobni brings the internet into Outlook…4 ways your Outlook will never be the same. Includes integration with Facebook, LinkedIn, Hoovers and Yahoo Mail.

The Windows Live Photo and Video Blog has a nice easy all in one place list of plugins for the betas of Live Photo Gallery and Live Movie Maker including Facebook, YouTube, SmugMug, Flickr, Picasa and Drupal.

I installed Expression Blend and am going to use it to try and produce some Silverlight content if I can. I found this site to be a good source of video training for Expression Blend, and the other Expression web products. Microsoft also has a Learning Snacks page with helpful videos about Silverlight.