Formula Errors

Fix Excel VLOOKUP #N/A for Phone Numbers in CSV Files

Rana GarciaRana Garcia Nov 14, 2026 868 views

Question details

The user needs to match phone numbers from CSV files with VLOOKUP without receiving #N/A errors.

Fix Excel VLOOKUP #N/A for Phone Numbers in CSV Files
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.
Before you start

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.

Solution 1Recommended

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.

1
Import the CSV

In Excel, select Data > From Text/CSV and choose the CSV file. Select Transform Data in the import preview.

2
Set the phone column to text

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.

3
Match the lookup values

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.

4
Use an exact match

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.

Import phone numbers consistently as text
Check for lost leading zeros: Changing an existing number to text does not restore zeros already removed during import. Reimport the original CSV as text if those zeros are still present in the file.
Work with CSV phone numbers

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. 1. Open the files: In WPS Spreadsheets, select Open and choose the CSV file and the workbook containing your lookup values.
  2. 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. 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. 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.
Clean phone-number lookup values with spreadsheet formulasUse VLOOKUP to match records across columnsOpen and edit Microsoft Excel XLSX and XLS filesWork with CSV data in a familiar spreadsheet interface
microsoft office alternative - wps office

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.