VLOOKUP errors—when using the Excel VLOOKUP function, you may encounter errors. The errors you may encounter can be broadly divided into two types: #N/A and #VALUE!.
Excel VLOOKUP Troubleshooting
Let’s look at the basic causes of Excel VLOOKUP errors and how to fix them.
Causes of VLOOKUP Errors
One cause of #N/A is that the lookup values have different formats. The data on the left is stored as numbers, while the data on the right is stored as numbers formatted as text. Although the displayed numbers are the same, the error occurs because values with different formats are used as the lookup value in the VLOOKUP function.
How to Fix VLOOKUP Errors
If an Excel function error occurs because the lookup values have different formats, change the format of the VLOOKUP lookup value to match the source data.
- Source data text format vs. lookup value number format
Use the TEXT function as shown below to change the format of the lookup value.
G4 cell =VLOOKUP(TEXT(B3,”##”),B8:D10,2,0)
- Source data number format vs. lookup value text format
Use the VALUE function as shown below to change the format of the lookup value.
G9 cell =VLOOKUP(VALUE(B8),B3:D5,2,0)
Conclusion
We covered how to resolve VLOOKUP errors. Let’s also briefly look at other Excel function errors.
- #NAME? error: This error occurs when a function name is entered incorrectly or the function does not exist. To resolve it, check the function name again and enter the correct function name.
- #VALUE! error: This error occurs when an argument is entered in an incorrect format or is in an invalid range. To resolve it, check the arguments again and enter them in the correct format.
- #DIV/0! error: This error occurs when you attempt to divide by zero. To resolve it, check for division by zero before performing the division and revise the formula to avoid dividing by zero.
- #REF! error: This error occurs when a cell reference is invalid. To resolve it, check the cell reference again and revise it to refer to the correct range.
- #NUM! error: This error occurs when a function returns an invalid value. To resolve it, check the function again and revise it to return a valid value.