Alternative lookup formulas
March 24, 2009 • JP • No Comments • Rate This Article
• Links to this article
I was reading Dealing with VLOOKUP and GETPIVOTDATA errors by Ken Puls and noticed he wraps VLOOKUP formulas in IF statements like this:
=IF(ISNA(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0, VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False))
The way it works is first, the code does the VLOOKUP and if the result is #N/A, it returns zero, otherwise it performs the VLOOKUP (again!) and returns the result.
The problem with this formula should be obvious: if your value exists, the VLOOKUP is performed twice! Imagine filling this formula down 10, 50, 100 columns and the resulting performance hit on your workbook.
A few of the commenters suggested alternatives which would work better. My favorite is the COUNTIF alternative:
=IF(COUNTIF(LeftmostColumn_of_rngToLookIn,rngToLookup),VLOOKUP(rngToLookup, rngToLookIn, ColToReturn, False), 0)
This formula uses COUNTIF to see if the value exists in the leftmost column (the column that VLOOKUP searches) and if it doesn't exist, it skips the VLOOKUP.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Programming exercises in Java, VBA, VB.NET
Next Post: Attach files in a folder to one email
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].