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. 🙂
11 thoughts on “Dealing with VLOOKUP and GETPIVOTDATA errors”
Prior to XL2K7, I used a much simplier IF() + VLOOKUP() combination. The IF()+ISNA() or it's counterpart IF()+ISERROR() both required having to use VLOOKUP() twice! Several years ago, I found I could use COUNTIF(). Since VLOOKUP() requires a value in the Left-most column of the range, all you have to do is determine if the value exists first before issuing the VLOOKUP(). Thus your above formula above would be re-written as:
=IF(COUNTIF(LookUpTablesLeftMostColumn, LookUpValue)>0, VLOOKUP(LookUpValue, LookUpTable,ColToReturn,False), 0)
Much less typing of that VLOOKUP() command!
Great tip, I'll definitely use this one!
We're going to start using Office 2007 in my department in the near future. In regards to this I'm on the lookout for some good resources about differences between Excel 2003 and 2007 and also possibly a list of new functions/functionality (like the IFERROR formula).
I'm not sure if it even exists, but I would very much like a summary of VBA pitfalls when developing in Excel 2007 from a Excel 2003 point of view. Also some perspective on what to be on the lookout for when converting old 2003 projects to 2007.
Any good recommendations on where to start reading up?
You could speed up the IF(ISNA) thing if you check for an error in MATCH instead of in VLOOKUP. I don't know what the difference in speed is, but MATCH is a lighter function than VLOOKUP.
Hi guys,
Nick, your approach is a little lighter, but to me it's still almost as bulky as writing the original approach. I haven't tested it myself, but I'd be concerned that the initial result would leave me open to a later error with the VLOOKUP if they didn't return the exact same results.
Peder, glad you found this useful. Sadly, the "What's New" help article in Excel 2007 doesn't actually list any of the new functions. The closest I found was this article on the Visio MVP's site. For VBA, there were some issues being tracked at VBA Express in this thread.
Jon, fair point on MATCH's speed. I always seem to reach to VLOOKUP first, as old habits die hard. It would still leave you with a pretty long formula though. I wish IFERROR had been there several versions ago, as this approach is much cleaner. (Would help to speed up to use MATCH within that, rather than VLOOKUP though.)
I suspect Nick's COUNTIF may be faster than MATCH. Someday I may even test them against each other.
Ken, I'm not sure where you would run into trouble.
Given:
Lookup Table = "A1:G100" on Sheet1
Lookup Value = "A1" on Sheet2
The formula in B1 on Sheet 2 would read:
=IF(COUNTIF(Sheet1!A1:A100,A1)>0,VLOOKUP(A1,Sheet1!A1:G100,5,FALSE),0)
if the countif fails (ie, returns 0), there's no reason to issue the VLOOKUP as it wouldn't find the value anyhow.
Ah! Okay, I get it. Sorry, just a little slow on the uptake there. 😉
That is interesting...
Pingback: Alternative lookup formulas - Code For Excel And Outlook Blog
You can write a VBA routined called IFERROR() for your 2003 deployment, which mimics the built-in version in 2007. After all upgraded to 2007, then delete the VBA function, and all will be right with the world...
@Nick, I tihink I figured this one out a few years ago, then forgot it. I knew there was an easier way, but I couldn't quite grasp it again. Thanks for bringing it up. I'm filling that one away.
Great tip.
Great tip Ken,
Yes, Vlookup is a very resource-intensive function
I did a simple test...
IFERROR is 30% faster than IF+ISERROR
If your sheet takes 7 minutes to calculate using the IFERROR formula, it would take 10 minutes with the IF+ISERROR version
And when I did the suggested test by Jon Peltier, I got these results...
IFERROR (with MATCH) is 10% faster than IFERROR (with VLOOKUP)