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.
Download Practice Workbook
You can download the practice book from the link below.
5 Common Reasons and Solutions for VLOOKUP Returning Just Formula Not Value
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.
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 formula instead of value in 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. For confirming whether this type of mistake is encountered or not, just double-click the formulated cell and take your cursor to front of the formula and check if there is extra space or not.
Follow the procedure stated below to fix this problem.
- 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.
- Now, press ENTER, and the function will return you the value and no more formula will be seen.
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 just selecting the formulated cell shows the colored region, which means the Show Formulas command is active.
Proceed as below to deactivate this command.
- 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.
So, all you have to do here is to take your mouse to the front of the formula and DELETE the Apostrophe symbol.
Removing the apostrophe will return 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. 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.
You need to change the cell format if you want the value, not the formula.
- 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.
- 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.
- 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.
💡 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.
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. For more queries, kindly visit our website.