The #VALUE! error appears when Excel can’t understand an argument in your formula. For example, the third argument for VLOOKUP is the column index number argument (**col index num**). This argument tells VLOOKUP which column of data to return and display. The correct example below shows a formula in cell I3 with the argument specified. The incorrect example shows that the formula is missing the argument, and therefore Excel displays the error.

## Correct

## Incorrect

Here are other reasons why the #VALUE error can occur with VLOOKUP. But if you aren't using VLOOKUP, check out the rest of this article for more things to try.

## Fix the error for a specific function

See more information at Correct the #VALUE! error in AVERAGE or SUM functions

See more information at Correct the #VALUE! error in the CONCATENATE function

See more information at Correct the #VALUE! error in the COUNTIF/COUNTIFS function

See more information at Correct the #VALUE! error in the DATEVALUE function

See more information at Correct the #VALUE! error in the DAYS function

See more information at Correct the #VALUE! error in the FIND/FINDB and SEARCH/SEARCHB functions

See more information at Correct the #VALUE! error in the IF function

See more information at Correct the #VALUE! error in the INDEX and MATCH functions

See more information at Correct the #VALUE! error in the FIND/FINDB and SEARCH/SEARCHB functions

See more information at Correct the #VALUE! error in AVERAGE or SUM functions

See more information at Correct the #VALUE! error in the SUMIF/SUMIFS function

See more information at Correct the #VALUE! error in the SUMPRODUCT function

See more information at Correct the #VALUE! error in the TIMEVALUE function

See more information at Correct the #VALUE! error in the TRANSPOSE function

See more information at Correct the #VALUE! error in the VLOOKUP function

If you don't see your function in this list, try the other solutions below.

## Other solutions to try

##
Try to locate the source of the error

You can try to locate the source of the error by selecting the cell with the error and clicking **Formulas > Evaluate Formula**. An interactive dialog will appear like this one:

Click the **Evaluate** button, and Excel will step through the parts of the formula individually. After each part is evaluated, it shows the evaluation to give you an idea of what's getting calculated. In this case the formula *=A2+B2+C2* breaks because of a hidden space in cell A2. You can't see it in cell A2. However, the wizard does see it, and it shows that the value is *=" "+B2+C2*. The " " indicates there's a blank space in cell A2.

##
Replace the #VALUE! error with something else

Sometimes you just want to replace the #VALUE error with something else like your own text, a zero or a blank cell. In this case you can add the IFERROR() function to your formula. IFERROR() will check to see if there’s an error, and if so, replace it with another value of your choice. If there isn’t an error, your original formula will be calculated. IFERROR will only work in Excel 2007 and later. For earlier versions you can use IF(ISERROR()).

**Warning:** IFERROR will suppress all errors, not just the #VALUE! error. It’s not advisable to use IFERROR until you are absolutely certain your formula works the way that you want. Otherwise, you may not see valuable error messages that indicate a problem.

Here’s an example of a formula that has a #VALUE! error due to a hidden space in cell D2:

And here’s the same example with IFERROR applied to replace #VALUE! with zero:

You could also use *=IFERROR(C2-D2,””)* to display nothing instead of 0, or even substitute your own text, like: *=IFERROR(C2-D2,”Discount Error”)*.

Unfortunately, you can see that IFERROR doesn’t actually resolve the error, it simply hides it. So be certain that hiding the error is better than fixing it.

##
Try using functions instead of operations

Formulas with math operations like +, -, *, ^ and / may not be able to calculate cells that contain text or spaces. In this case, try using a function instead. Functions will often ignore text values and formulate everything as numbers, eliminating the #VALUE! error.

##
Check for hidden spaces inside cells

Sometimes cells look blank, when in fact they may have hidden spaces. Double-click a cell that your formula is referencing, and check for spaces. In the following illustration, there are extra spaces to the left of the cursor in cell A2.

Try deleting the spaces, or select the cell and press Delete to see if the error goes away. If you have many rows to check, you can use the ISBLANK() function in an empty column to see if cells are truly blank or not. In the following illustration, cell A2 has a hidden space that you can’t see, and the ISBLANK function in E2 returns FALSE. Whereas A3 is truly blank and the ISBLANK function returns TRUE.

##
Clean text using the CLEAN function

Sometimes when you import data from external sources, it can come into Excel with non-printing characters like ^ or '. Unfortunately, these characters can cause problems in formulas and it can be hard to delete them. However, you can use the CLEAN function to strip the characters out, and convert the values from text to numbers. You can then copy the CLEAN function range and paste just the cell values back over the original range. Here’s how: Select the CLEAN function range, and then press **CTRL+C**. Select the original cells and click **Home > Paste > Paste Special > Values**.

##
Clean text using the TRIM function

Sometimes leading or trailing spaces can cause problems. Excel will generally try to remove these spaces in simple values. For example, it will remove spaces if you type “ 123”, but it might not be able to do the same with date values like “ 1/1/16”. In this case, you can use the TRIM function to remove those leading and trailing spaces, and then reference the converted TRIM value in your formula.

In this example, leading spaces in the date in A2 cause a #VALUE! error with *=B2-A2*. But by using *=TRIM(A2)*, we can then use *=B2-D2* to resolve the error.

##
Check if the formula is referencing cells that contain text

Right-click a cell that the formula is referencing, and then click **Format Cells**. Make sure the format is not **Text**. Sometimes this is not practical for more than one cell. If you have more than one to check, insert a new column and use the ISTEXT() function to see if the cells are formatted as text. In the example below “173 0” isn’t a valid number, so Excel will see it as text. This can often be the result of a typo. Note that ISTEXT won’t resolve the error, it will just tell you if text could be causing the issue.

##
Make sure data connections are available

Your data connection may have become unavailable at some point. To fix this, restore the data connection, or consider importing the data if possible. If you don't have access to the connection, ask the creator of the workbook to make a new file for you. The new file ideally would only have values, and no connections. They can do this by copying all the cells, and pasting only as values. To paste as only values, they can click **Home** > **Paste** > **Paste Special** > **Values**. This eliminates all formulas and connections, and therefore would also remove any #VALUE errors.

##
Post a question in the Excel Community Forum

If you’re not sure what to do at this point, you can search for similar questions in the Excel Community Forum, or post one of your own.