Fix Excel VLOOKUP #N/A for Phone Numbers in CSV Files
Question details
The user needs to match phone numbers from CSV files with VLOOKUP without receiving #N/A errors.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Phone numbers imported from CSV files are used as lookup values in an Excel workbook.
- Observed behavior
- VLOOKUP returns #N/A when phone numbers have different data types or contain hidden characters.
Identify the lookup value and the corresponding phone-number column in the source file. Check whether leading zeros, country codes, spaces, or punctuation are significant in your phone numbers.
Import phone numbers consistently as text
Use this method when phone numbers are stored as text in one file and as numbers in another, especially when leading zeros matter.
In Excel, select Data > From Text/CSV and choose the CSV file. Select Transform Data in the import preview.
In the Power Query editor, select the phone-number column and set its data type to Text. If an automatic Changed Type step converted it to a number first, remove that step before setting the column to Text.
Import or enter the phone numbers used for the lookup as text as well. Compare a few matching cells, including any leading zeros and country codes.
Enter a formula such as =VLOOKUP(D2,$A$2:$B$100,2,FALSE), where D2 is the lookup phone number and column A is the phone-number column in the lookup range.

Clean hidden characters in both phone-number columns
Use helper columns when phone numbers appear identical but still do not match.
Match CSV phone numbers in WPS Spreadsheets
Use WPS Spreadsheets to inspect imported phone numbers, clean common hidden characters, and run an exact-match VLOOKUP.
- 1. Open the files: In WPS Spreadsheets, select Open and choose the CSV file and the workbook containing your lookup values.
- 2. Check the imported values: Click a phone-number cell and inspect its value in the formula bar. Compare leading zeros, country codes, and punctuation with the lookup value.
- 3. Clean both columns: In helper columns, apply =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to each phone-number column, adjusting A2 to reference the appropriate cell.
- 4. Run the lookup: Enter a VLOOKUP formula using the cleaned lookup value and a range whose first column contains the cleaned source numbers. Use FALSE as the last argument.

Frequently Asked Questions
Why does VLOOKUP return #N/A when the phone numbers look the same?
One value may be text while the other is numeric, or one may contain a hidden character. Inspect both cells and use matching data types and cleaned helper columns.
Can a CSV file preserve Excel's phone-number formatting?
No. CSV stores plain text, not Excel cell formatting. Import the phone-number column as Text through Data > From Text/CSV to help preserve characters such as leading zeros.
How do I remove nonbreaking spaces from imported phone numbers?
In a helper cell, use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) and fill the formula down. Apply the same cleanup to the values on both sides of the lookup.
Can I recover leading zeros by formatting cells as text?
No. Formatting an already imported number as text does not restore removed zeros. Reimport the original CSV with the phone-number column set to Text if the zeros are present in the source.




