Share via

Why does VLOOKUP sometimes return the formula in the cell instead of the value?

Anonymous
2013-07-10T02:13:21+00:00

VLOOKUP sometimes returns the formula in the cell instead of the value. I resolve it by formatting the cell as General or Number depending on what I'm looking up, then click fx again to reopen the function arguments box, then click OK. How else can I get around this so VLOOKUP recognises the result as general or number, rather than entering the whole argument string in the cell?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-07-10T23:01:10+00:00

    ToneML wrote:

    I'm in Workbook 2, looking up information from Workbook 1, and the response in the cell is this =VLOOKUP(A3,'C:\Users\user\Documents\xxxxxx\Sales Reports[Item Listing at 30th May 2013.xlsx]Sheet1'!$A$2:$G$2146,7,FALSE), instead of the number 446 as it should be. I've found a work around by checking the cell format and find it reads Text, so I change it to Number and re-do the process. When I reopen the VLOOKUP dialog box the parameters are still there so I click OK and the problem is fixed.

    [....] 

    Why is all this happening, and how to prevent it? The origin of the two workbooks is an accounting program where I export Sales and Stock reports to Excel to create a monthly analysis.

    The problem seems to be the cell that contains the VLOOKUP formula, not the data that is in the "Item Listing at 30th may 2013.xlsx" workbook.

    It appears that the cell containing the VLOOKUP formula is incorrectly formatted as Text when the formula is entered.  Consequently, the "formula" is interpreted as text, not a formula.

    Simply changing the cell format does not immediately change the type of cell contents.  The "formula" is still interpreted as text.  But by "re-entering" the formula -- either by pressing F2, then Enter or by clicking on "fx" to open the function dialog box as you do -- the cell contents are re-interpreted, this time as a bona fide formula.

    How to avoid that?  "Don't do that"! ;-)  (Old vaudeville joke.)

    You say the two workbooks are created by an accounting program.  Does that mean the accounting program is creating the workbook and the VLOOKUP from scratch?

    If that is the case, your only remedy is to get some tech support from the suppliers of the accounting program.

    ([EDIT] That is, your "only remedy" other than what you are doing already, with the procedural improvements that Vijay suggested.)

    But I suspect that is not what you mean.  If so, you might need to fill in some details.  Bottom line:  how does the cell that contains the VLOOKUP formula become formatted as Text in the first place?

    We cannot answer that.

    90+ people found this answer helpful.
    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-07-10T04:52:39+00:00

    Once you have changed the formatting as General or Number and now wants to recalculate your formula without clicking F2 and enter or click fx and enter.

    The easiest way is select a single cell or whole sheet,

    Press CTRL+H

    Give "="(equal to) without inverted comma in both Find and Replace box.

    Replace All.

    It will force excel to recalculate all formulas and will do what you are looking for.

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2015-09-09T11:24:57+00:00

    I found this solution at excelvlookuphelp.com that answers your question

    Potential Reasons and Solutions

    First, check that there is an equals sign (=) ahead of your formula.

    Next, check that there isn’t an apostrophe (‘) before your formula.

    Then click on the Formulas Tab to see that the “Show Formulas” button isn’t on.

    ![](http://i1.wp.com/www.excelvlookuphelp.com/wp-content/uploads/2015/02/ShowFormulas.jpg?zoom=3&resize=470%2C183)

    The Show Formulas option should be deselected if you want to see the formula result

    Lastly, check that the cell isn’t formatted as text. If it is, then change it to General.

    ![](http://i2.wp.com/www.excelvlookuphelp.com/wp-content/uploads/2015/02/General-Format.gif?zoom=3&resize=304%2C220)

    Source: www.excelvlookuphelp.com

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-02-10T14:45:52+00:00

    The answer by (V_agarwal) is the correct answer and not the answer which was wrote by (joeu2004 ) .

    i cannt understand why the person that had asked the question ignored what (V_agarwal)  had wrote and went one asking  the same question Again and Again and Again and Again .

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2013-07-11T02:03:29+00:00

    ToneML wrote:

    To confirm, I get the two reports from the accounting program monthly, then I use VLOOKUP to take some relevant information from one to supplement the other, to create my final report. This works very well apart from the formatting issue.

    As you concluded, the issue is with the cells receiving the data in the main workbook.

    So, has your question been answered?

    Ensure that the cell(s) with the VLOOKUP formula are not formatted as Text before you enter the formula.

    If you are looking for something more automated, we could talk about things you might do in a VBA macro.  But I would need more information.  The devil is in the details.  IMHO, it is more trouble than it's worth.  But that's really up to you to decide.

    2 people found this answer helpful.
    0 comments