Fix VLOOKUP Returning #N/A When Adding a New Excel Worksheet
Question details
The user's VLOOKUP formula returns an #N/A error when trying to look up account numbers on a newly added worksheet, despite working perfectly on existing sheets.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Using VLOOKUP across multiple worksheets to cross-reference specific account numbers.
- Observed behavior
- The formula returns #N/A for the newly added sheet, typically caused by identical-looking account numbers being stored as text on one sheet and as numbers on another.
Verify that the lookup value actually exists in the newly added worksheet's table array before troubleshooting formatting and data type issues.
Identify and Convert Inconsistent Data Types
Check if your account numbers are stored as text on one sheet and numbers on another, which is the most common cause of the #N/A error.
VLOOKUP requires exact matches in both value and data type. If an account number is stored as text (e.g., '12345') on the source sheet but as a numerical value (12345) on the destination sheet, VLOOKUP will fail to recognize them as a match.
Select your column of account numbers and set the alignment to 'General'. By default, numbers align to the right side of the cell, while text aligns to the left.
In an empty adjacent column, type `=ISNUMBER(A2)` and `=ISTEXT(A2)` (assuming A2 is the first account number). This will return TRUE or FALSE, clearly indicating how the data is stored.
If the cells are stored as text, highlight the entire column. Click the small yellow diamond with an exclamation mark that appears next to the selection, and choose 'Convert to Number'.
Alternatively, select the column, navigate to the 'Data' tab on the ribbon, click 'Text to Columns', and simply click 'Finish' without changing any settings. This forces the spreadsheet to re-evaluate the entries as numbers.

Remove Hidden Spaces from Lookup Values
Eliminate invisible leading or trailing spaces that prevent VLOOKUP from finding exact matches.
Master VLOOKUP and Data Analysis with WPS Spreadsheet
WPS Spreadsheet provides powerful data formatting tools, smart error checking, and seamless performance, making it simple to resolve VLOOKUP data-type mismatches and analyze complex datasets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file where your VLOOKUP is returning the #N/A error.
- 2. Highlight problematic data: Select the column containing the account numbers in the newly added worksheet.
- 3. Use smart error correction: Click the smart exclamation mark icon next to the selection and choose 'Convert to Number' to instantly harmonize data types.
- 4. Check your results: Your VLOOKUP formula will automatically recalculate and display the correct corresponding values.

Frequently Asked Questions
Why does VLOOKUP return #N/A when the exact value is visibly there?
This usually happens when the data formats do not match. Even if they look identical, one cell might be stored as a number and the other as text. Alternatively, there may be invisible leading or trailing spaces attached to one of the values.
Can I force VLOOKUP to match text with numbers within the formula?
Yes. If your lookup value is a number but the lookup array contains text, you can append an empty string to force it to text: `=VLOOKUP(A2&"", Range, 2, FALSE)`. If the lookup value is text but the array has numbers, convert it to a value using `=VLOOKUP(VALUE(A2), Range, 2, FALSE)`.
Does changing the cell format to 'General' fix the VLOOKUP #N/A error?
No. Changing the cell format from the ribbon to 'General' or 'Number' changes how new data will be entered, but it does not alter the underlying data type of existing entries. You must use 'Text to Columns', the VALUE function, or the 'Convert to Number' smart tag to fix existing data.




