Home > Formulas > VLOOKUP > Errors ## Excel Formula Error ValuesWhat the Excel formula errors mean, and how to fix them. Make your own hash errors, to use with formulas. Create short troubleshooting messages with ERROR.TYPE function |

When there are problems with an Excel formula, Excel shows its built-in error values, like #N/A, or #REF! See an example of what causes each type of error value, and where you can get help on fixing those problems.

Here is a list of the built-in Excel formula error values. Microsoft calls them hash errors, because of the character at the beginning.

- #NUM!
- #VALUE!
- #N/A
- #DIV/0!
- #REF!
- #NAME?
- #NULL!

There is a brief note on each hash error below, and a link to the related Microsoft help page,

**Meaning**: Numeric values aren't valid, or an iteration function can't find a result, or the formula result is a number that's too large or small to be shown.

**Troubleshooting Info**: #NUM! Help

**Example**: The DATEDIF formula in cell D4 returns the #NUM! error because the end date is earlier than the start date. Correct one of the dates, to fix the error.

Meaning: Something is wrong with the formula or the cells that it references. Microsoft admits that this error value is vague, and it can be difficult to find the cause.

Troubleshooting Info: #VALUE! Help

**Example**: The simple formula in D4 returns the #VALUE! error, because there is a space character in C4. Sometimes people do that to "clear" a cell. To fix the error, select cell C4 and press the Delete key.

**Meaning**: The formula can't find the thing it was asked to look for, usually with VLOOKUP, MATCH, etc.

**Troubleshooting Info:** #N/A Help

**Example**: The formula in C4 returns the #N/A error because the Item in B4 was not found in the lookup range. To fix the error, type an "s" at the end of "Pant", in cell B4.

**Meaning**: The divisor is a zero, and Excel can't divide by zero.

**Troubleshooting Info:** #DIV/0! Help

**Example**: The formula in D4 returns the #DIV/0! error because there is nothing entered in C4. A blank cell is equal to zero, and Excel cannot divide by zero. To fix the error, type a number in cell C4.

**Meaning**: The formula refers to a cell that isn't valid. Perhaps a column or row is deleted, or a VLOOKUP range has 3 columns, and you ask for a result from column 4.

**Troubleshooting Info**: #REF! Help

**Example**: The formula in C4 returns the #REF! error because the lookup range has only 2 columns and the VLOOKUP formula refers to column 3. To fix the error, change the column number to 2.

**Meaning**: Usually caused by a type in a function name or a defined name. Or, you put text into a formula, but didn't enclose it with double quote marks

**Troubleshooting Info**: #NAME? Help

**Example**: The formula in C4 returns the #NAME? error because the defined name "TaxRate" is spelled incorrectly. To fix the error, correct the spelling in the formula.

**Meaning**: The formula refers to a range that doesn't exist. Often caused by the intersection operator (the space character)

**Troubleshooting Info**: #NULL! Help

**Example**: The formula in D4 returns the #NULL! error because there is a space character (intersection operator) between the cell references, instead of a comma, and those 2 cells do not intersect. To fix the error, type a comma after the B4 reference.

The Excel ERROR.TYPE function can identify specific types of errors, and you can use that information to help with troubleshooting worksheet formulas.

In the video below, I show examples of using the ERROR.TYPE function for formula troubleshooting

The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.

The ERROR.TYPE function has the following syntax:

**ERROR.TYPE(error_val)**

There is one required argument:

**error_val**-- the error that you want to identify

The ERROR.TYPE function returns a code number, if an error is found, using one of the following codes:

- 1…..#NULL!
- 2…..#DIV/0!
- 3…..#VALUE!
- 4…..#REF!
- 5…..#NAME?
- 6…..#NUM!
- 7…..#N/A

If no error is fount, the formula returns **#N/A**

With the ERROR.TYPE function you can check a cell, to identify which error it contains.

In the screen shot below, I've entered the following formula in cell C3, to check for an error in cell B3: **=ERROR.TYPE(B3)**

In this example, cell B3 contains a #VALUE! error, so the error type is 3.

By combining the ERROR.TYPE function with other functions, you can help people troubleshoot error results in a cell.

In the example shown below, numbers should be entered in cells B3 and C3.

- B3: enter the student score (number)
- C3: enter the total score available (number)
- D3: formula to calculate score percent:
**=IF(C3="","",B3/C3)**

The result in cell D3 should be a number, but could return an error in some cases:

- If
**text**is entered in either cell, the result in D3 is a #VALUE! error. - If a
**zero**is entered in cell C3, the result is a #DIV/0! error.

In cell D4, another formula shows a troubleshooting message, when needed.

In cell D4, I've entered the following formula, which uses ERROR.TYPE to get an error code number, if cell D3 contains an error:

**=IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3), $B$9:$B$15, $D$9:$D$15),””)**

Here's how the formula works:

**ISERROR**checks for an error-
**ERROR.TYPE**function returns a number for the error. **LOOKUP**function returns the applicable troubleshooting message from a table of error type codes

Here is the error code lookup table, with code numbers, error values, and messages.

Instead of using Excel's built-in hash errors, you can create your own hash errors. Thanks to UniMord for this suggestion.

**Warning: **Use this technique with discretion, because your co-workers might not like it! Also, be sure to **add notes to the workbook**, or cell comments, explaining what your custom hash errors mean.

The examples below show how to create custom hash errors with the IF function, and with a custom number format, and you can download the sample file, to see both examples.

In the screen shot below, the budget limit is 6000. In the total cell (B8), there is an IF function that checks the sum. If the amount is over the budget limit, a custom hash error appears – **#OVER!**

**=IF(SUM(B2:B7)>D2, "#OVER!", SUM(B2:B7))**

Because the error looks like the built-in hash errors from Excel, people might pay attention to it.

You can also show a custom hash error with conditional formatting. In this example, there is a conditional formatting rule on the Total cell (B8), to check if the total is greater than the budget limit (D2)

**=B8>D2**

If that condition is met, the cell shows a custom number format:

**"#OVER!"**

**Errors**: Download the zipped Excel Formula Errors file. The zipped file is in xlsx format, and does not contain any macros.

**ERROR.TYPE**: To follow along with the ERROR.TYPE video, you can download the ERROR.TYPE function file. The zipped file is in xlsx format, and does not contain any macros.

These resources can help you troubleshoot Excel errors, and help prevent errors from appearing in your workbook.

Charles Williams is an Excel calculation expert, and his FastExcel add-in can help you find calculation bottlenecks in your workbook, and understand and debug complex formulas more easily.

Learn about its troubleshooting tools and features, to see how it can help you.

Don't waste time looking for errors **after** your Excel file is completed. Check for errors from the beginning.

In his online course, Ken Puls shows how to build an error checking system in your workbooks. See how to create an error checking section on each worksheet, and a global check point for the entire workbook. The system even checks the pivot tables, to see if they've been updated -- I've been hit by that problem!

This small investment could save you hours of headaches! There are two versions of the course:

- online videos, with downloadable Excel workbooks
- online and downloadable videos, with downloadable Excel workbooks

Use the Excel add-in, RefTreeAnalyser, to audit your formulas. There is a free trial version available.

This add-in helps with easy auditing of formula dependents and precedents, finding circular references, checking for formula inconsistancies, and many other auditing tasks.

Last updated: February 21, 2024 1:54 PM