Several weeks ago I got a flurry of questions about Excel lookup techniques. I also got surprised questions during an Excel seminar this month, when I mentioned that I never use VLOOKUP, and discourage its use.

So that topic is ripe for a series of blog entries.
 

As I write at http://exceluser.com/blog/, VLOOKUP, HLOOKUP, and LOOKUP all have limitations that INDEX-MATCH doesn't.

However, ALL Excel lookup functions, including MATCH, share one significant problem: The default lookup method can give you incorrect results. The blog entries I've posted so far explain this problem in detail.

That said, VLOOKUP has fundamental limitations that make it not worth using, in my opinion. I'll cover these in my next blog entry, then show why INDEX-MATCH is a lot more powerful and useful.
 

Speaking of errors, New Excel has a function that makes it much easier to trap errors in formulas. In Error Handling in Excel Spreadsheets, I explain why IFERROR was created and how it's used.

I must admit that I have a special reason for liking this function: I first suggested it to Microsoft several years ago. When I asked a manager on the Excel team about it, he said they created the function because they agreed with my suggestion.
 

Most Excel reports contain static data. That is, they have numbers in cells. In my experience, this is a horrible way to structure reports, because Excel users need to manually massage the data each period. And that leads to Spreadsheet Hell.

Instead, most reports should contain only a few settings, and formulas that return data from a simple database. With this design, we update the database each period and use the data in any number of reports.

To update a report, we just change the date in one cell of the report, recalculate, and print.

If you're using any of my dashboard products with an Excel database, you're using one version of this concept. But there's another version, which can be used with transaction-type data and extensive filtering. And both versions can be used with standard reports, as well as dashboards.

I'm working on a training series that will explain Dynamic Spreadsheet Reporting techniques in detail. To do so, I'll cover spreadsheet functions and techniques that I used extensively during my consulting days.
 

Earlier this week I discovered the FAST Modeling Standard for spreadsheets here:
http://www.fast-standard.org/the-standard/

When I glanced through their "best-practice" standard, I discovered an astonishing paragraph:

"The FAST Modeling Standard advocates very limited use of Names, that is adherents to the Standard are generally against Names. Adherents of the FAST Modeling Standard believe that Names positively harm flexibility and transparency; benefits they may provide are often achievable through simpler techniques and design. In fact, Names are better (or only) suited to simple spreadsheets with limited complexity, where reading a simple natural language formula such as = Price * Quantity is a real possibility."

Because I had discovered FAST through LinkedIn's group, "Financial Modelling in Excel", I opened a new discussion, "The FAST Standard opposes Excel range names in most instances. Why? Does anyone agree?"

If you're on LinkedIn, you might want to check out Monday's discussion. It was rather intense for a while.
 

In my e-book, I recommended that you find well-designed charts and tables in business magazines and recreate them in Excel. The last chapter of the book includes several examples.

I've followed my own advice since about 1990, and I've got binders filled with clippings of those professionally designed figures. I flip through the binders frequently when I need an idea for a new design for a chart or table. But 99% of that material is going to waste.

Also, I've got dozens -- maybe hundreds -- of useful charts and tables that I've created in the past 20 years. But they're just sitting there, gathering digital dust.

So here's what I'm going to do...

In several weeks I'm going to offer a very low-cost subscription to Charley's Swipe Files. Twice a month I'll send a link to a zip file that includes versions of these charts and other displays for both Classic and New Excel. The zip also will include short documentation for the files.

If you print the first page of the, documentation and add it to a binder, you'll be able to flip through it, find useful displays, and use them in your work.

Additionally, of course, if you copy the Excel files, and then experiment with them, you'll learn all sorts of techniques that I know you'll find useful.

More later,

Charley