Excel Hashtag # Errors – causes and solutions

Hashtag # errors can creep into your spreadsheets and indicate that the formula calculation has resulted in an error.  Here we'll explore some of the most common # errors and, more importantly, how to overcome them.

#N/A

 

This error is most common when using the VLOOKUP or HLOOKUP formula.  It occurs when the value you are looking for does not exist in the range you are searching.  It can be useful if you are checking data, but there is a neater way of handling this and returning something more meaningful.

If you add the IFERROR formula around your lookup, you can return a word or value which indicates that the value could not be found.  For example:

=IFERROR(VLOOKUP(A1, B2:C12, 2, FALSE),"Not Found")

Alternatively, if you want to check ONLY for the #N/A error you can use the IFNA formula instead of IFERROR (Excel 2013 or later only).

#DIV/0!

If the cell you are trying to divide by is zero, Excel is unable to calculate the result so returns the "divide by 0" error.  Again, you can add a more meaningful outcome (e.g. default the value to 1) by encapsulating the original formula in the IFERROR function:

=IFERROR(A1/A2,1)

#REF!

The #REF! error usually occurs because a cell or range name which was referred to in your formula no longer exists.  This can happen if, for example, you delete a worksheet from the workbook which a formula refers to.  You can use the IFERROR formula as shown above, but this can be quite dangerous and it's better to track down what the formula SHOULD have been referring to and fix the problem at source!

#VALUE!

This error can occur for many reasons, but two of the most common ones are:

• You try to perform a mathematical calculation on a text cell.  Supposing you have a cell with a text string and you try to add 10 to this cell; you will get the #VALUE! error as this is an invalid calculation.
• You perform a FIND or SEARCH function on a text string and the character you are trying to find does not exist in the string.

Again, you can use the IFERROR formula to trap and account for these errors.  So, in the second example, you might use the following formula:

=IFERROR(FIND(A1,A2,1),"Character not found")

If the character in A1 does not exist in cell A2, you will then see the "Character not found" result instead of the #VALUE! error.

 

These are just a few examples of how # errors can occur.  If you are seeing errors which aren't included or are having trouble finding out why they happen, please get in touch.

 

NB!  The IFERROR formula was introduced in Excel 2007.  If you are using an older version of Excel, please contact us for alternative solutions.