[Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)

The sample dataset contains information from a store with the items’ codes, names, and sales.

Due to format errors, the VLOOKUP function returns #N/A errors instead of results.

Problem 1 – VLOOKUP Not Working Due to Mismatch of Cell Formats

A difference in format between the lookup value and column value will result in this error. In this example we want to find the item name whose sales value is $20,000.

=VLOOKUP(F5,C5:D14,2,FALSE)

The formula is returning a #N/A error because the lookup value cell F5 is in Accounting format, but the lookup column values are in Text format.

Check this by selecting cells C5:C14 >> Home tab >> Number group >> Number Format box.

To solve this issue follow the steps below.

Steps:

Click on cells C5:C14.
Go to Data tab >> Data Tools group >> Text to Columns tool.

The Convert Text to Columns Wizard window will appear.
Choose the Delimited option and click on the Next button.

In the Convert Text to Columns Wizard, check on the option Tab from the Delimiters options.
Click on the Next button.

Choose the General option from the Column data format pane and click on the Finish button.

All the data of C5:C14 cells will be converted from Text to General format. The VLOOKUP function should work properly now.

Note:

You can change the format of the C5:C14 cells manually by using the Format Cells dialogue box, but you have to delete the values of all the cells, change the format and then put the values again manually one by one to make the VLOOKUP function work.

Problem 2 – Hidden Space/Characters

Another common cause of errors are hidden spaces or characters. This type of issue mainly happens with numbers.

In this example we want to find item names according to the item codes.

=VLOOKUP(F5,$B$5:$D$14,3,FALSE)

But, the formula is returning a #N/A error for some reason.

If you click on cell B9 where the relevant Item Code is, there is an apostrophe (‘) just before the actual item code. This apostrophe is not shown in the cell output, but it is converting the code’s format from Number to Text automatically.