I need to thank Joe Chirilov and Danny Khen, from the Excel team at Microsoft for this tip. We were discussing some Excel things while we were having dinner during the summit, and I was bemoaning the fact that it would be really nice to have an additional argument for the VLOOKUP function that returned a value instead of #N/A if nothing was found in the list.
The method I had been using was the tried, tested and true approach. Here's what my VLOOKUP would look like:
=IF(ISNA(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0, VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False))
Now, it's no secret that VLOOKUP is a pretty heavy formula. If you don't believe that, stick a thousand in a worksheet and see how much it starts to slow down. If you count the function calls in the above, you'll see that instead of calling VLOOKUP once, I had to call it twice, as well as calling both the IF and ISNA functions as well. Four functions to return one result. And while I haven't formally benchmarked the speed, I can tell you that my workbooks were running very slowly.
Both Joe & Danny looked at me kind of funny and asked why I wasn't using the IFERROR formula. My response was something really clever, like "Huh?"
I'm really glad that we had the conversation at all. This program is so big, that things just slip in during releases that we either don't hear about, or I don't notice. 🙂
The IFERROR approach to this issue is far superior to the old method, in my opinion. Using IFERROR, the same VLOOKUP results can be achieved with:
=IFERROR(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0)
Much better!
What really spurred the discussion was that I'd also been working on (and still am) a set of financial statements that are driven by pivot tables. In making heavy use of the GETPIVOTDATA function, I was running into the same problems there: If an account/department combination didn't exist for the month, I would get a #REF! error. To solve this, I'd wrap them up in an IF/ISERR combination like this:
=IF(ISERR(GETPIVOTDATA("Amount",PT_Actual_YTD!$A$5,"GLMonth",rngMonthEnd,"Account",$D102,"Dept",$E102)),0,GETPIVOTDATA("Amount",PT_Actual_YTD!$A$5,"GLMonth",rngMonthEnd,"Account",$D102,"Dept",$E102))
With IFERROR, the formula compresses down to:
=IFERROR(GETPIVOTDATA("Amount",PT_Actual_YTD!$A$5,"GLMonth",rngMonthEnd,"Account",D46,"Dept",E46),0)
In my opinion, IFERROR is a far superior approach than the prior. It is so much easier to:
- Write
- Read
- Maintain
So far as I can see, it pretty much wins on all counts but two…
- This function is only available in Excel 2007+. Open the file in a previous version of Excel and all those awesome formulas return #NAME? errors. 🙁
- It is not generic enough to capture the difference between #N/A, #REF! and other errors. While this isn't an issue for me, I'm sure someone will have a practical use why they might want to react differently if one type of error was returned vs another.
At any rate, as great as this function is, and as much time as it will save me in the long run, it didn't go in without issue for me. Naturally, all of my financial information also has to be sent to head office. And what do they use? Why Excel 2003, of course! In some ways I'm kind of glad that I didn't put that together immediately, so I had half my file converted to IFERROR before I realized the issue. Had I not done so, I might have stuck with the slower approach, even though I need the time most at month end. Instead I wrote a utility to copy the sheets they need to a new workbook and break all the links back to the original source. A little bit painful to have to do, but at least I can still take advantage of the new approach, and they can have files without #NAME? references in them. 🙂