Fixing VLookup errors using IFERROR

//Fixing VLookup errors using IFERROR

vlookup-iferror

To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. The IFERROR function allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally, there is no error and the looked up value is returned. If VLOOKUP returns the #N/A error, IFERROR takes over and returns the value you supply.

Fixing VLookup errors using IFERROR will take few steps:

  • While writing lookup formulas like VLOOKUP, INDEX+MATCH it is common to search for values that do not exist in your data. You can wrap such formulas in IFERROR for peace of mind.

Ex: =IFERROR(VLOOKUP(…),”Not found”)

  • While using reference formulas like INDEX, OFFSET, frequently, we try to fetch the data that is not in the list of values. This returns #REF errors. You can fix them with IFERROR easily.

Ex: =IFERROR(INDEX(…),””)

  • While using arithmetic, numeric expressions, usually we end up dividing by 0. You can fix such things by using IFERROR.

Ex: =IFERROR(AVERAGE(…),”0″) — Returns 0 when the list has zero values.

Turtortek is founded by IT professionals with more then 20 years experience and having done management certification from IIM Kolkata. We are focused on delivering project based quality IT trainings in Cloud Computing, Big Data & Hadoop, Analytics, Testing, Web Technologies and Android where the emphasis is mainly on real-time project based learning. We provide instructor led class-room, online training and self-learning video tutorials.

No comments yet.

Leave a comment

Your email address will not be published.