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

Get FREE Advanced Excel Exercises with Solutions!

Excel VLOOKUP function is a fantastic function to look up values vertically from a dataset. But, due to different types of errors, the VLOOKUP function does not work properly. The most common problem that happens in this regard is due to format. In this article, I will discuss the Excel VLOOKUP function not working due to format issues and respective solutions.

VLOOKUP Not Working Due to Format in Excel: 2 Solutions

Say, you have a sample dataset of a store with the items’ codes, names, and sales.

Sample Dataset for VLOOKUP Not Working Due to Format

Now, you want to use the VLOOKUP function to look up the names of particular items according to their codes or sales. But, due to format errors, you are finding #N/A errors instead of actual results. Now, why is it happening? And what are the solutions? Explore the article below to learn about this thoroughly.

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

The first factor that can result in this error is the different format of lookup value and lookup column values. Say, you want to find the item name whose sales value is $20,000.

So, you have inserted the VLOOKUP function formula perfectly as follows.


Excel VLOOKUP Not Working Due to Format

But, 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.

You can check this by selecting cells C5:C14 >> Home tab >> Number group >> Number Format box.

Check the Format of Following Cells

🧲 Solution:

Now, to solve this format issue and to work out the formula properly, go through the steps below.

📌 Steps:

  • First and foremost, click on cells C5:C14.
  • Following, go to Data tab >> Data Tools group >> Text to Columns tool.

Access the Text to Columns Tool

  • As a result, the Convert Text to Columns Wizard window will appear.
  • Subsequently, choose the Delimited option here and click on the Next button.

Convert Text to Columns Wizard Step 1 Window

  • Afterward, for step 2 of the Convert Text to Columns Wizard, check on the option, Tab from the Delimiters options.
  • Subsequently, click on the Next button.

Convert Text to Columns Wizard Step 2 Window

  • Afterward, in the third step, choose the option General from the Column data format pane and click on the Finish button.

Convert Text to Columns Wizard Window to Solve VLOOKUP Not Working Due to Format

As a result, all the data of C5:C14 cells will be converted from Text to General format. And, the VLOOKUP function would work properly now which would result in the desired item name for the given sales value. And, the output would look like this now.

Solved VLOOKUP Not Working Due to Format Issue


You can change the format of the C5:C14 cells manually by using the Format Cells dialogue box. But, in this way, you can not just select all cells and change the format to make it work. Rather, 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 problem that is considered to initiate #N/A error in the VLOOKUP function, is hidden spaces or characters. This type of issue mainly happens with numbers.

Say, you want to find item names according to the item codes. Now, you have inserted the formula perfectly as below.


Excel VLOOKUP Not Working Due to Format

But, the formula is returning a #N/A error for some reason. Now, what is the reason? Let’s find out.

Here, let, you are finding the item name for item code 1612005. This is actually belonging to cell B9. Now, to find the problem if you click on cell B9, you would see 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.

Hidden Character Within Numbers

So, you have found out that this apostrophe is the problem.

🧲 Solution:

Now, to get rid of this problem and make your VLOOKUP function work, follow the steps below.

📌 Steps:

  • First, click on your formula cell (G5 here).
  • Afterward, incorporate the TRIM function into cell F5 and cell range B5:D14 in the existing formula. So, the formula would be as follows.


Incorporating TRIM Function to Solve VLOOKUP Not Working Due to Format Issue

Thus, you will get the VLOOKUP function working perfectly now and you will get your desired result as your issue has been solved.

Download Practice Workbook

You can download our practice workbook from here for free!


So, in this article, I have shown you 2 suitable solutions for the Excel VLOOKUP function not working due to format. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

Thank you!

Related Articles

<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF