Whether it's #VALUE!, #NAME! or a problem with VLOOKUP, the following information can help you correct your error.
VLOOKUP
If you're getting errors or unexpected results with VLOOKUP, download the VLOOKUP troubleshooting tips quick reference.
#VALUE! error
The source of this problem is usually related to having a mix of numeric values and text values. For more information, see Correct a #VALUE! error.
#NAME? error
Make sure you type names correctly, enclose any text in quotation marks, or enclose sheet names in single quotation marks ('). For other causes and solutions, see #NAME? error.
#NUM! error
This can happen if you typed extra characters in a formula. For example, don't type $1,000 in a formula. Instead, enter 1000. For other causes and solutions, see Correct a #NUM! error.
#N/A error
If a formula can’t find a referenced value, it returns the #N/A error. For more information, see Correct a #N/A error.
#REF! error
Excel shows this error when a formula contains a cell reference that isn’t valid. For more information, see Correct a #REF! error.
#DIV/0! error
This happens when a number is divided by zero (0), or when a formula refers to a cell that has 0 or is blank. For more information, see #DIV/0! error.
Referencing other sheets and workbooks
If you are referencing another worksheet name, type ! after the name, and then type a cell or range. If the sheet name has spaces, enclose the name in single quotation marks. For example: =SUM('Sales Report'!A1:A8).
When referencing another external workbook:
-
Enclose the workbook name in square brackets [ ].
-
Type the full path to the file.
-
Enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point).
Example: =SUM('/Users/yourname/Desktop/[Q2 Operations.xlsx]Sales'!A1:A8)
Other quick solutions that might help
-
Start every formula with an equal sign (=). Example: =SUM(A1:A8)
-
Use the * symbol to multiply numbers, not an X. Example: =A1*A8
-
Match all opening and closing parentheses so that they are in pairs. This example has 2 pairs: =IF(40>50,SUM(G2:G5),0)
-
Enter all required arguments. The Formula Builder can help you with this. Start typing a formula with a function name, and then press CONTROL + A to see the Formula Builder.
-
Use quotation marks around text in formulas. Example: =IF(A2>B2,"Over Budget","OK")
-
Change a referenced cell's data type. Press
