How to Fix VLOOKUP #N/A Error Caused by Mismatched Formats
Question details
The user needs to resolve a VLOOKUP #N/A error that occurs because lookup codes are formatted as numbers in one dataset and text in another.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up values (such as NACE codes) across different workbooks where leading zeros or differing cell formats cause a mismatch.
- Observed behavior
- The VLOOKUP formula returns an #N/A error, even though the lookup values in both spreadsheets visually appear to be identical.
Select the cells containing your lookup codes and check if they align to the left or have a small green triangle in the corner, which indicates a number is currently stored as text.
Standardize Lookup Values Using the TEXT Function
Wrap your lookup value inside the TEXT function to match the formatting of the target array, which is especially useful for preserving leading zeros.
If your destination table contains codes formatted as text with a specific length (like 4 digits), VLOOKUP will fail if your source data is formatted as pure numbers. By converting the lookup value to text dynamically within the formula, you eliminate the mismatch without modifying the raw data.
Click on the cell where you want the VLOOKUP result to appear.
Type the VLOOKUP formula and wrap the lookup cell reference in the TEXT function. For example, to format the value in B2 as a four-digit text string, use: =VLOOKUP(TEXT(B2,"0000"), '[file B analysis.xlsx]Sheet1'!$B$2:$F$33, 5, FALSE).
Press Enter to execute the formula. Click the bottom-right corner of the cell and drag it down to apply the corrected formula to the rest of the column.
Convert Cell Formats using Text to Columns
Batch convert an entire column of data so that both the source and lookup arrays share the exact same underlying data type.
Convert Text to Numbers Using the VALUE Function
If your lookup table contains true numbers but your lookup value is stored as text, use the VALUE function to convert it on the fly.
Effortlessly Fix VLOOKUP Errors with WPS Spreadsheet
WPS Office offers a fully-featured Spreadsheet tool that flawlessly handles complex formulas, data conversion, and lookup functions like VLOOKUP, XLOOKUP, and TEXT. Fix your #N/A errors smoothly without worrying about compatibility.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbooks containing the mismatched lookup data.
- 2. Use built-in Error Checking: Select cells with green warning triangles, click the warning icon, and choose 'Convert to Number' for instant formatting.
- 3. Apply lookup formulas: Type your =VLOOKUP or =XLOOKUP formulas exactly as you would in Microsoft Excel, utilizing TEXT or VALUE functions as needed.
- 4. Save your work seamlessly: Save your finished document in standard .xlsx format, ensuring perfect compatibility with colleagues who use other office suites.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the values look exactly the same?
This happens when the data types differ. For example, one cell might contain the pure number 1234, while the other contains the text string '1234'. VLOOKUP requires identical data types to recognize a match, so it fails even though they visually appear identical.
Does changing the cell format to 'Number' fix the VLOOKUP error?
Usually, no. Simply changing the format using the Home tab dropdown changes how the cell is displayed, but doesn't change the underlying data type. You must use the Text to Columns tool, double-click the cell and press Enter, or use formulas like VALUE to force the conversion.
How do I know if a number is stored as text in my spreadsheet?
By default, numbers stored as text align to the left side of the cell, whereas true numbers align to the right. Additionally, you may see a small green triangle in the top-left corner of the cell warning you of the mismatch.
Can I use XLOOKUP instead of VLOOKUP to bypass this error?
XLOOKUP is a powerful and more flexible alternative available in newer versions of Excel, but it still requires the data types to match. However, just like with VLOOKUP, you can use =XLOOKUP(TEXT(B2,"0000"),...) or =XLOOKUP(VALUE(B2),...) to achieve a perfect match.
