While doing VLOOKUP function in Excel, we may come across some instances where instead of displaying the desired values, Excel shows #NA values due to error checking features and datatype confusion.
This issue often arises due to Excel’s error checking features. When a green triangle appears on selected cells, it signifies an error that Excel has identified. The error checking feature alerts users to potential issues with the data, such as inconsistent formulas or invalid references.
To prevent the green triangles and suppress error-checking in Excel, follow these steps:
- Go to the “File” tab in Excel and select “Options.”
- In the Excel Options dialog box, click on “Formulas.”
- Uncheck the checkbox labeled “Enable background error checking.”
When exporting Excel files from NetSuite, datatype confusion may lead to errors and green triangles. For example, when exporting data from a free text field that contains numbers, Excel may recognize the data as text, resulting in VLOOKUP errors.
To resolve this, you can click on the cell with green triangle, hover over the yellow warning icon and select “Convert to Number”, that appears along with other options to ensure that Excel recognizes the cell’s content correctly.