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.

⏩ **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.

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

**Trailing Space**.

**Read More: ****[Fixed!] VLOOKUP Not Working Between Sheets (8 Easy Fixes)**

### 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.

⏩ **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.

**Read More: ****[Solved]: Excel VLOOKUP Not Working with Numbers**

### 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.

⏩ **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.

**Read More: ****[Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)**

### 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.

- Just disable this option and click
**OK**.

Performing this task will return the value instead of formula.

**Read More: ****[Fixed!] Excel VLOOKUP Returning #N/A Error (6 Possible Solutions)**

## 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.

**Read More: ****[Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value**

#### 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.

**Read More: ****[Fixed!] Excel VLOOKUP Function Not Calculating Automatically**

## 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. For more queries, kindly visit our website.

Stay connected!