Whether it's #VALUE!, #NAME! or a problem with VLOOKUP, the following information can help you correct your error.
If you're getting errors or unexpected results with VLOOKUP, download the VLOOKUP troubleshooting tips quick reference.
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.
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.
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.
If a formula can’t find a referenced value, it returns the #N/A error. For more information, see Correct a #N/A error.
Excel shows this error when a formula contains a cell reference that isn’t valid. For more information, see Correct a #REF! 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 + 1, and then select Number.