Excel getpivotdata function true5/31/2023 With IFERROR, the formula compresses down to: To solve this, I'd wrap them up in an IF/ISERR combination like this: 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. 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. =IFERROR(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0) Using IFERROR, the same VLOOKUP results can be achieved with: The IFERROR approach to this issue is far superior to the old method, in my opinion. This program is so big, that things just slip in during releases that we either don't hear about, or I don't notice. I'm really glad that we had the conversation at all. ![]() My response was something really clever, like "Huh?" And while I haven't formally benchmarked the speed, I can tell you that my workbooks were running very slowly.īoth Joe & Danny looked at me kind of funny and asked why I wasn't using the IFERROR formula. 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. If you don't believe that, stick a thousand in a worksheet and see how much it starts to slow down. Now, it's no secret that VLOOKUP is a pretty heavy formula. =IF(ISNA(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0, VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False)) The method I had been using was the tried, tested and true approach. ![]() 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. I need to thank Joe Chirilov and Danny Khen, from the Excel team at Microsoft for this tip.
0 Comments
Leave a Reply. |