Excel VLOOKUP #N/A With Matching Data: Fix by Resetting Cell Format to General

Your VLOOKUP formula returns #N/A even though you can see the lookup value in the source table. This error means Excel cannot find a match. A common cause is a mismatch in data types caused by cell formatting. A number stored as text will not match a true number, even if they look identical. This article explains why this happens and provides a step-by-step method to fix the #N/A error by resetting cell formats to General.

Why VLOOKUP Returns #N/A for Seemingly Matching Data

The VLOOKUP function performs an exact match by comparing the underlying data in cells, not just their visual appearance. The most frequent cause of a false #N/A is a data type mismatch. For example, your lookup value might be the number 100, but in your lookup table, the value 100 is stored as the text string “100”. To Excel, these are two completely different items.

This often happens when data is imported from other systems, copied from web pages, or when cells have been pre-formatted as Text. A telltale sign is a small green triangle in the corner of a cell, which Excel uses to flag a “number stored as text” error. Another clue is cell alignment; text defaults to left-aligned, while numbers default to right-aligned in the General format.

How Cell Formatting Controls Data Type

The Number Format in the Home tab does not change the actual data. It only changes how the data is displayed. Setting a cell to the Text format *before* entering a number instructs Excel to treat that entry as text. The solution is not just to change the display format, but to reset the cell’s state and then re-recognize the data correctly, which is what the General format facilitates.

Steps to Reset Cell Format and Fix VLOOKUP

Follow these steps to change the data type of your lookup value or your lookup table data from text to a number. Apply these steps to the range causing the mismatch.

1. Identify the mismatched data
   Select the cell you are using for the lookup value and the corresponding cell in your lookup table’s first column. Look for the green error indicator triangle or check the alignment.
2. Select the problematic cell or range
   Click on the cell, or click and drag to select multiple cells that contain the data causing the #N/A error.
3. Open the Format Cells dialog
   Right-click on the selected cells and choose Format Cells from the context menu. Alternatively, press Ctrl+1 on your keyboard.
4. Set the number format to General
   In the Format Cells dialog box, click the Number tab. In the Category list on the left, select General. Click OK to apply the change.
5. Re-enter the data or force a recalculation
   Simply changing the format may not be enough. Click into the formula bar for a cell and press Enter. For a whole range, you can use a faster method: select the range, go to Data > Text to Columns, and immediately click Finish in the wizard that appears.
6. Verify the VLOOKUP result
   Return to your worksheet cell containing the VLOOKUP formula. The #N/A error should now be replaced with the correct lookup result. If not, press F9 to force a worksheet recalculation.

Alternative Method Using Paste Special

If the Text to Columns method does not work, you can use a calculation to convert the data.

1. Enter the number 1 in a blank cell
   Type 1 into any empty cell and copy it by pressing Ctrl+C.
2. Select your text-number range
   Highlight the cells containing the numbers stored as text.
3. Open Paste Special
   Right-click on the selected range, choose Paste Special, and then click the Paste Special option at the bottom of the menu.
4. Choose the Multiply operation
   In the Paste Special dialog, under Operation, select Multiply. Click OK. This multiplies all selected values by 1, converting text to numbers.
5. Clear the copied cell
   Delete the cell where you typed the number 1.

If VLOOKUP Still Shows #N/A After Formatting

Resetting the cell format solves the data type mismatch. If the error persists, other issues are causing the #N/A result.

Lookup Value Contains Extra Spaces

Leading, trailing, or multiple spaces in cells are not visible but break exact matches. Use the TRIM function. Change your lookup value to =TRIM(A2) to remove extra spaces before VLOOKUP uses it.

Table Array Reference is Incorrect

The #N/A will persist if your VLOOKUP’s table_array argument does not include the column containing the matching data. Ensure the first column of your defined table_array range is the column you are searching against. Also, verify that the range is not shifted by inserted rows or columns.

Exact Match vs. Approximate Match Confusion

VLOOKUP’s fourth argument, range_lookup, controls the match type. For an exact match, you must set this to FALSE. A missing or TRUE argument causes Excel to search for an approximate match, which can return #N/A if the first column is not sorted. Always use FALSE for exact matches: =VLOOKUP(value, table, col_index, FALSE).

Data Type Correction Methods Compared

Item	Format Cells to General	Text to Columns	Paste Special Multiply
Primary Use	Resetting cell state for manual re-entry	Batch conversion of text to numbers	Forcing conversion via a calculation
Speed for One Cell	Fast	Overly complex	Overly complex
Speed for a Range	Slow, requires editing each cell	Very fast, one-click finish	Fast, but requires a helper cell
Changes Original Data	Only after re-entering value	Yes, converts in place	Yes, converts in place
Best For	Understanding the root cause	Fixing large imported data sets	Stubborn cells that resist other methods

You can now fix the frustrating VLOOKUP #N/A error by correcting underlying data type mismatches. The quickest method for a range of data is Data > Text to Columns. Remember to always set the fourth VLOOKUP argument to FALSE for exact matches. For advanced data cleaning, combine TRIM with Text to Columns to remove spaces and convert formats in one action.