Saturday, November 21, 2009

Excel functions I use daily


  • IF



  • YEAR



My favorite function mashup is to combine IF, ISERROR, and VLOOKUP to return either a value from another table, or 0 (or blank, if you prefer). Here's how to do it:

=IF(ISERROR(VLOOKUP([Std vlookup formula])),0,(VLOOKUP([Std vlookup formula])))

Here's the logic: If the Vlookup returns an error (i.e., the value of the referenced cell does not exist in the lookup range), then return a value of Zero, else return the value found in the lookup range.

Why? It gets rid of those nasty "#N/A" errors.

No comments:

Post a Comment