VLOOKUP Is Returning Just Formula Not Value in Excel

Get FREE Advanced Excel Exercises with Solutions!

The VLOOKUP function is used to extract a value from a lookup array. Due to some mistakes encountered in the formula, the VLOOKUP function may keep returning the formula itself instead of the value. This article will find the reasons why VLOOKUP is returning just formula, not value.


VLOOKUP Is Returning Just Formula Not Value: 5 Reasons and Solutions

Let’s say, we have a dataset of some sellers and their corresponding sales over a specific period of time. From this dataset, we want to find out the sales of a random seller by applying the VLOOKUP function here just like the image described below.

But somehow, the formulated cell is not returning the value, rather it is showing the formula.

VLOOKUP Not Returning Value Just Formula

We don’t want to show the formula in that cell, we just want the corresponding value. So, we need to find out the reasons why VLOOKUP is returning just formula instead of value and ways to fix this problem

In this section, you will find 5 common reasons for returning the formula instead of value in the case of applying the VLOOKUP function. I will demonstrate them one by one here.


1. Leading Space (Before Equal Sign) in Formula

One of the most common cases of showing formula in an Excel cell is the use of a leading space in front of the equal (=) sign. To confirm whether this type of mistake is encountered or not, just double-click the formulated cell and take your cursor to the front of the formula and check if there is extra space or not.

Follow the procedure stated below to fix this problem.

Steps:

  • First of all, double-click the cell or just press F2 to go to Editing Mode. Remove the leading space. As soon as you delete the space, the lookup_value and lookup_array will be shown as a selected region on the worksheet. That means the previous form was not actually a formula.

VLOOKUP Not Returning Value Just Formula for Leading Space

  • Now, press ENTER, and the function will return you the value and no more formula will be seen.

Note: Same process for Trailing Space.

Read More: [Fixed!] Excel VLOOKUP Not Returning Correct Value


2. Enabled Show Formulas Command

Sometimes the cell may show just the VLOOKUP formula not the value, if the Show Formulas command is active. There is a simple way to check whether this is encountered or not. If you double-click a cell containing the VLOOKUP formula, the lookup_value, and the lookup_array will be selected as colored regions. But when just selecting the formulated cell shows the colored region, it means the Show Formulas command is active.

VLOOKUP Not Returning Value Just Formula (for Show Formulas Command

Proceed as below to deactivate this command.

Steps:

  • Firstly, go to the Ribbon > click the Formulas tab > check that the Show Formulas option is active. Click it to deactivate.

  • Hence, the formula will not be seen anymore and the value will be restored.


3. Presence of Apostrophe (‘) Character

An Apostrophe () before the equal sign in the formula is responsible for not returning the value, just the formula.

VLOOKUP Not Returning Value Just Formula for Apostrophe

So, all you have to do here is to take your mouse to the front of the formula and DELETE that Apostrophe symbol.

Removing the apostrophe returns the value, not the formula.


4. Cell Formatted as Text

When a cell is formatted as Text, any change in the cell doesn’t update instantly due to the format. If you enter a formula in a cell that is formatted as Text, the cell will not calculate the value and will return the formula itself.

VLOOKUP Not Returning Value Just Formula for Text Format

You need to change the cell format if you want the value, not the formula.

Steps:

  • First of all, go to the Home tab > click the dropdown of the Number Format box > select a format (i.e. General / Number).

  • Then, double-click the cell again to activate Edit Mode followed by pressing ENTER.

  • The cell will return the value instead of the formula.


5. ‘Formula Display’ Activated in Advanced Excel Options

If the Show Formulas option is activated in the Advanced Excel options, the formulated cell will not return the value.

Proceed like the steps below to deactivate the option.

Steps:

  • First, click the File option on the Ribbon.

  • Then, select Options from the menu list.

  • Now, the Excel Options command box will appear. In the Advanced option, check the Display options for the worksheet group. You will see that this option is enabled.

VLOOKUP Not Returning Value Just Formula for Advanced Excel Options

  • Just disable this option and click OK.

Performing this task will return the value instead of formula.


Common Problems and Solutions for VLOOKUP Malfunctioning

Let’s check out some common problems associated with the VLOOKUP function.


VLOOKUP Not Working

When the VLOOKUP function doesn’t work properly, it results in a #N/A error in the formulated cell. The reasons behind the VLOOKUP #N/A error are discussed below.


1. Leading or Trailing Space in Lookup Value

If the cell containing lookup_value includes either a leading or trailing space character, the function will not work and result in a #N/A error.

💡 Quick Solution: Just remove the extra space to fix the error.


2. Exact Match Not Found

When you use the Exact Match argument in the VLOOKUP function and the exact match is not found in the lookup range, the cell will show #N/A error.

VLOOKUP Not Returning Accurate Value and Showing Error

💡 Quick Solution: Correct the spelling of the lookup value to match the lookup array.


3. Approximate Match

The Argument for an approximate match in the VLOOKUP function is TRUE. For the approximate match in the VLOOKUP function, #N/A error can occur if the smallest value in the lookup_array is greater than the lookup_value.


VLOOKUP Not Returning Correct Value

Sometimes the formulated cell may show a #VALUE error. This may happen from:

  • If the lookup_value contains more than 255 characters.
  • If the col_index_num is less than 1. Actually, this argument can be less than returned from another function nested in it.
  • If you want to extract data from another workbook and the full destination path is not specified properly.
  • If the lookup column contains duplicates, the function extracts the first match.

VLOOKUP Formula Not Calculating

If the Calculation Options is set Manual, then then the VLOOKUP formula may not calculate. Set it to Automatic to get rid of this.


Download Practice Workbook

You can download the practice book from the link below.


Conclusion

In this article, I have tried to show you some reasons why VLOOKUP is returning just formula not value in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.

Stay connected!


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.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo