Spy Journal 3.0 Filtering the web for you

When I wrote the how to use VLOOKUP and HLOOKUP article a few weeks ago I hinted at writing how to use the COLUMN, ROW, MATCH, INDEX and OFFSET functions to enhance the use of the VLOOKUP and HLOOKUP functions. Today we will look at the use of MATCH.

First of all here is a common scenario.

You create a table of say 4 columns, and then in another sheet you create a VLOOKUP function that returns results from the 4th column. EG =VLOOKUP(lookup_value,table_range,4,FALSE) where the 4 relates to the 4th column. This works great until one day you (or your colleagues) delete the 3rd column in the table, or insert another column in the middle of the table. Now your VLOOKUP fails as the 4th column either doesn’t exist, or is now actually the 5th column. In this case the VLOOKUP formula you created is just not flexible enough to handle the changes.

The following explanation builds on the dynamic range name process defined in my article written back in 2004. You will need to create a dynamic range name (in this example called table_header) that works on the header row of the table you are performing the VLOOKUP on.

In Excel 2007 Use the Name Manager from the Formula tab. In Excel 2003 and earlier use Edit Insert Name Define. In both versions CTRL F3 is the keyboard short cut.

More interesting things I have found since yesterday.

Australian residents can go in the draw for a Windows 7 T-shirt courtesy of Long Zheng.

And if you are running Windows 7 some important news about the UAC. – especially take note of the the last paragraph which says:

Update: I’d also like to reiterate, until the RC build of Windows 7 is available, everyone using the Windows 7 Beta should change their UAC setting to “max” to ensure they are safe from either UAC vulnerabilities.

This article is in a direct response to an article on Slate subtitled How Gmail destroyed Outlook. Farhad Manjoo has written an article explaining how to use Google Gears to take your Gmail account offline. However he has badly titled it The Best E-Mail Program Ever and subtitled it How Gmail destroyed Outlook.

If he had just left it titled something like Google Gears provides Gmail with offline capability I wouldnt have minded. But to attempt to use that feature to claim that Gmail is the best program ever or to even more ridiculously claim that Gmail has somehow destroyed Outlook as a result is farcical at best and completely misleading and untrue.

OK lets look at what he says and why.

I’ve got lots of interesting things to share today.

If you like medieval siege equipment then this YouTube video of Mongo the Trebuchet will fascinate you and if you don’t have any particular interest than perhaps just the sight of a flaming fireball being hurled through the air might. Link thanks to Geeks are Sexy.

Another “heads up” from the Sexy Geeks (pun intended) concerns Terry Pratchett and some fancy hi tech science helping him fight Alzheimers.

A Drupal group meetup is on in Brisbane next week – I’m going.

Two new OneNote blogs – from guys in the Microsoft One Note team – John Guin writes on One Note Testing and Jeff Cardon on OneNote Tips & Tricks

Hard Drive Not Found and Data Recovery with Windows Home Server another great story on why to have a Windows Home Server. We are setting up our second one now.

Windows 7. There’s been lots of build up and very little hype. The beta is out and being installed by large numbers of people. I have mine downloaded but haven’t had time to set up a test machine for it yet.

Here are some reviews blogs and other items.